Description:
This is similar to #10396, but different. See there.
It is very often task with RDBM to insert a row, and if the row exists, then rather update some of it's values. MySQL 4.1 introduced "INSERT... ON DUPLICATE KEY UPDATE ..." syntax, which I found very useful and it allows me create very ellegant shortcuts; but still most often I need to UPDATE the row with the values specified in the INSERT part.
How to repeat:
Let's have:
CREATE TABLE t (user VARCHAR(16) PRIMARY KEY NOT NULL, val1 VARCHAR(15) DEFAULT 'Still empty', val2 VARCHAR(15));
INSERT INTO t SET user='ilona', val1='Ahoj Lidi', val2='ABC';
Today, to blindly insert OR update _only_new_values_ in Ilona's record, I need to do:
INSERT INTO t SET user='ilona', val2='EFG' ON DUPLICATE KEY UPDATE val2='EFG';
or
INSERT INTO t SET user='ilona', val2='EFG' ON DUPLICATE KEY UPDATE val2=VALUES(val2);
Suggested fix:
I suggest to allow this syntax:
INSERT INTO ... SET ... ON DUPLICATE KEY UPDATE;
E.g.
INSERT INTO t SET user='ilona', val2='EFG' ON DUPLICATE KEY UPDATE;
This would UPDATE Ilona's record to be like:
+-------+-----------+-----+
| ilona | Ahoj lidi | EFG |
+-------+-----------+-----+
Just a note - if I used REPLACE, the row would look like this:
+-------+-------------+-----+
| ilona | Still empty | EFG |
+-------+-------------+-----+
I am sure that many developers, especially PHP devs, would highly appreciate this possibility, which would save them dozens of code lines in every project - no need to try to SELECT, then INSERT or UPDATE.
