Bug #11442 INSERT... ON DUPLICATE KEY UPDATE; w/ values taken from the INSERT part

Submitted: 20 Jun 2005 2:25 Modified: 27 Dec 2006 8:42
Reporter: Ondra Zizka Email Updates:
Status: Verified Impact on me:
None 
Category:MySQL Server: DML Severity:S4 (Feature request)
Version:5.0 OS:Any (All)
Assigned to: CPU Architecture:Any
Tags: insert duplicate key update

[20 Jun 2005 2:25] Ondra Zizka
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.
[27 Dec 2006 8:42] Valeriy Kravchuk
Thank you for a reasonable feature request.

Portions of this website are copyright © 2001, 2002 The PHP Group

Page generated in 0.049 sec. using MySQL 8.4.7-u6-cloud

Timestamp references displayed by the system are UTC.
Content reproduced on this site is the property of the respective copyright holders.
It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.