I update/insert values in a single table with the ON DUPLICATE KEY UPDATE
function. So far everything is fine.
INSERT INTO table1 SET field1=aa, field2=bb, field3=cc
ON DUPLICATE KEY UPDATE SET field1=aa, field2=bb, field3=cc;
But now I would like to achieve that the update only is done if a condition (WHERE
) is true.
Syntactically not correct:
INSERT INTO table1 SET field1=aa, field2=bb, field3=cc
ON DUPLICATE KEY UPDATE SET field1=aa, field2=bb, field3=cc WHERE field4=zz;
Any ideas how the correct SQL statement is?
Thanks a lot.
ON DUPLICATE KEY UPDATE is a MariaDB/MySQL extension to the INSERT statement that, if it finds a duplicate unique or primary key, will instead perform an UPDATE. The row/s affected value is reported as 1 if a row is inserted, and 2 if a row is updated, unless the API's CLIENT_FOUND_ROWS flag is set.
The Insert on Duplicate Key Update statement is the extension of the INSERT statement in MySQL. When we specify the ON DUPLICATE KEY UPDATE clause in a SQL statement and a row would cause duplicate error value in a UNIQUE or PRIMARY KEY index column, then updation of the existing row occurs.
So yes it is atomic in the sense that if the data that you are trying to insert will cause a duplicate in the primary key or in the unique index, the statement will instead perform an update and not error out.
When you insert a new row into a table if the row causes a duplicate in UNIQUE index or PRIMARY KEY , MySQL will issue an error. However, if you specify the ON DUPLICATE KEY UPDATE option in the INSERT statement, MySQL will update the existing row with the new values instead.
Using IF() should work, though it's not nice:
INSERT INTO table1 SET
field1=aa,
field2=bb,
field3=cc
ON DUPLICATE KEY UPDATE SET
field1 = IF( field4 = zz, aa, field1 ),
field2 = IF( field4 = zz, bb, field2 ),
field3 = IF( field4 = zz, cc, field3 )
Only update the fields with new values if the condition is met, otherwise keep the old ones.
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With