StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

PO
text
Body
copied!<p>The only way that you could use the <code>insert .... on duplicate key...</code> syntax to solve your issue would be if <code>name</code> and <code>email</code> made a composite key - and I think you would be better off using an auto_increment as a primary key.</p> <p>You might have to put a little logic into the PHP to do a check first, then insert or update - or write a function to do that same test for you - but neither will be a simply query that you can just fire off.</p> <p>Edit: Not sure if this is what you want to do, but I think that the best solution for your requirements is to actually use two tables in a one to many relationship.</p> <pre><code>Table-Users id | email | name create table users( id int(10) not null auto_increment, email varchar(100), name varchar(100), primary key(email, name) ); Table-Ideas id | userID | idea create table users( id int(10) not null auto_increment primary key, userID int(10) not null, idea text ); </code></pre> <p>With the primary key on the table, you can safetly do an <code>insert... duplicate...</code> without worrying about over-writing folks. The second table will however allow you to have the ideas stored safetly locked to the user, and let you have a number of ideas per user. As the relationship is the <code>users.id</code> to <code>ideas.userID</code> you won't lose who owns it even if their details are updated.</p> <p>Edit: (aka, ZOMG <em>facepalm</em>)</p> <pre><code>$query=" update users set name='".$userName."' where email='".$userEmail."' and name is null"; </code></pre> <p>Edit 2: (aka, <em>wipes brow</em>)</p> <pre><code>insert into users select email, '".$namn."' from users where email='".$epost."' on duplicate key update name = values (name); </code></pre> <p>and here it is working:</p> <pre><code>mysql> create table test1 (myName varchar(10) unique, myEmail varchar(10)); Query OK, 0 rows affected (0.02 sec) mysql> insert into test1 values ('Tom','something'); Query OK, 1 row affected (0.01 sec) mysql> insert into test1 values('Nick',null); Query OK, 1 row affected (0.00 sec) mysql> select * from test1; +--------+-----------+ | myName | myEmail | +--------+-----------+ | Tom | something | | Nick | NULL | +--------+-----------+ 2 rows in set (0.00 sec) mysql> insert into test1 select myName, myEmail from test1 where myName='Tom' on duplicate key update myEmail = values (myEmail); Query OK, 0 rows affected (0.00 sec) Records: 1 Duplicates: 0 Warnings: 0 mysql> select * from test1; +--------+-----------+ | myName | myEmail | +--------+-----------+ | Tom | something | | Nick | NULL | +--------+-----------+ 2 rows in set (0.00 sec) mysql> insert into test1 select 'Tom', myEmail from test1 where myName='Tom' on duplicate key update myEmail = values (myEmail); Query OK, 0 rows affected (0.00 sec) Records: 1 Duplicates: 0 Warnings: 0 mysql> select * from test1; +--------+-----------+ | myName | myEmail | +--------+-----------+ | Tom | something | | Nick | NULL | +--------+-----------+ 2 rows in set (0.00 sec) mysql> insert into test1 select myName, 'Something Else' from test1 where myName='Tom' on duplicate key update myEmail = values (myEmail); Query OK, 2 rows affected, 1 warning (0.01 sec) Records: 1 Duplicates: 1 Warnings: 1 mysql> select * from test1; +--------+------------+ | myName | myEmail | +--------+------------+ | Tom | Something | | Nick | NULL | +--------+------------+ 2 rows in set (0.00 sec) mysql> insert into test1 select myName, null from test1 where myName='Nick' on duplicate key update myEmail = values (myEmail); Query OK, 0 rows affected (0.00 sec) Records: 1 Duplicates: 0 Warnings: 0 mysql> select * from test1; +--------+------------+ | myName | myEmail | +--------+------------+ | Tom | Something | | Nick | NULL | +--------+------------+ 2 rows in set (0.00 sec) mysql> insert into test1 select myName, 'yay' from test1 where myName='Nick' on duplicate key update myEmail = values (myEmail); Query OK, 2 rows affected (0.01 sec) Records: 1 Duplicates: 1 Warnings: 0 mysql> select * from test1; +--------+------------+ | myName | myEmail | +--------+------------+ | Tom | Something | | Nick | yay | +--------+------------+ 2 rows in set (0.00 sec) </code></pre> <p>Edit 3: Try this for your <code>$query</code></p> <pre><code>insert into table1 select coalesce(email,'".$epost."') as email, coalesce(name,'".$namn."') as name from table1 where email='".$epost."' on duplicate key update name = values (name); </code></pre>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload