Bug #10396 UPDATE... [ON NONE FOUND INSERT]
Submitted: 5 May 2005 19:03 Modified: 13 Jun 2005 22:59
Reporter: Michael Winston Email Updates:
Status: Verified Impact on me:
None 
Category:MySQL Server: Command-line Clients Severity:S4 (Feature request)
Version:4.1 OS:Any (any)
Assigned to: CPU Architecture:Any
Triage: Triaged: D5 (Feature request)

[5 May 2005 19:03] Michael Winston
Description:
This feature request is inspired by the "INSERT... ON DUPLICATE KEY UPDATE" syntax, which is just so darn useful.

The request is for something similar, but for UPDATE.  If there are zero records matched on an UPDATE command, the user can INSERT a new record.  For example,

table: names
-------------
| id | name | age |
| 1 | Jack | 25 |
| 2 | Walter | 35 |
-------------

UPDATE names SET age = '45' WHERE name = 'Tony' ON NONE FOUND INSERT SET age = '45', name = 'Tony'

table: names
-------------
| id | name | age |
| 1 | Jack | 25 |
| 2 | Walter | 35 |
| 3 | Tony | 45 |
-------------

I hope this isn't a totally boneheaded idea, but it sure would be nice to not have to check for the existence of a record to figure out whether to UPDATE or INSERT (as least, from this PHP programmer's point-of-view).

How to repeat:
100% not a bug.  Feature request.
[13 Jun 2005 22:19] Ondra Zizka
This is almost what I wanted to submit as a feature request, just I would prefer INSERT with a simple UPDATE option, and it would work this way:

   CREATE TABLE tabulka ( id INT PRIMARY KEY, val VARCHAR(10) DEFAULT 'Default', val2 VARCHAR(10));
   INSERT INTO tabulka SET id = 1, val = 'Ahoj lidi.', val2 = 'ABC';
   INSERT INTO tabulka SET id = 1, val2 = 'EFG' ON DUPLICATE KEY UPDATE; /* ! */
   SELECT * FROM tabulka;
   --> id = 1, val = 'Ahoj lidi', val2 = 'EFG'

That means, without PKey match, 2nd INSERT would  set "val2" to 'EFG' and "val" to default value ("Default"); but in this case, "INSERT INTO" would become UPDATE and the row would be UPDATED, that means, only what was SET would change, and the rest would remain intact. 

To achieve this effect now (4.1.12), you must duplicate everything from SET in the UPDATE part:
   INSERT INTO tabulka SET id = 1, val2 = 'EFG' ON DUPLICATE KEY UPDATE val2='EFG';

That's what I miss _very_ much and what would save me dozens of code lines in every project. I consider submitting another feature request as this is slightly diffrent.
[13 Jun 2005 22:37] Ondra Zizka
As I compare that two posts, it seems we are willing for the same thing. But I find my syntax more logical:

   INSERT INTO ... SET ... ON DUPLICATE KEY UPDATE;
   /* empty update statement updates according to the INSERTed values */

And I totally agree that
"it sure would be nice to not have to check for the existence of a record to figure out whether to UPDATE or INSERT (as least, from this PHP programmer's point-of-view)."
[13 Jun 2005 22:59] Michael Winston
It seems to me that you want something completely different than my initial request.  You want a modification of the already-existing "INSERT INTO ... ON DUPLICATE KEY UPDATE", which first tries to INSERT and then UPDATEs.

I want some new functionality that first tries to UPDATE and, if no matches are found, INSERTs.

In my initial example, imagine that the 'id' column is an auto-incrementing, primary key field.  Under your syntax (i.e., what already exists), I would need to lookup the id before execution so that the INSERT fails and the UPDATE gets triggered.  That really saves me no work except that I could roll my 2 if/then/else'd queries into 1 longer query.
End result: 2 queries (one for the key lookup and one for the INSERT...UPDATE)

Under my syntax, you don't need to know the key value to trigger the backup functionality.  It just requires the UPDATE to fail before INSERTing.  This allows for a greater lattitude in your WHERE clause.
End result: 1 query (UPDATE...INSERT)

So, I don't think we want the same thing.  You might want to spin yours out into a new feature request.
[13 Jun 2005 23:10] Ondra Zizka
Yes, I see... I didn't notice the "WHERE name = 'Tony'" part and flash-read it as

  UPDATE names SET age = '45', name = 'Tony' ON NONE FOUND ...

IMHO that would not be quite easy to implement and has little chance to be accepted, but nice idea, too.
[13 Jun 2005 23:16] Ondra Zizka
>> and has little chance to be accepted, but nice idea, too.

Amongst other reasons, because stored procedures will be ready to use in production soon, and this is exactly the case where I would use it rather than making MySQL's syntax more complex and more standard-less.
[20 Jun 2005 11:48] Ondra Zizka
Please have a look at #11442 - http://bugs.mysql.com/bug.php?id=11442