Menu
When using insert... on duplicate key update, what is the syntax to update multiple columns?
INSERT INTO table1 (col1, col2, col3, col4) VALUES (’$val1’, ‘$val2’, ‘$val3’, ‘$val4’) ON DUPLICATE KEY UPDATE col2=‘$val2’, col3=‘$val3’, col4=‘$val4’ // <-- not sure Update: I am using this within PHP. Since this is a syntax question, it very relevant.
$result = mysql_query("INSERT INTO table1 (col1, col2, col3, col4) VALUES (’$val1’, ‘$val2’, ‘$val3’, ‘$val4’) ON DUPLICATE KEY UPDATE (col2=‘$val2’, col3=‘$val3’, col4=‘$val4’)") Again, not sure about this last part with the "Update".
544
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 first step incurs a disk seek on large data sets with an ad-hoc primary (or unique key). And that is why it is slow. So, the moral of the story is this. In MySQL, “insert … on duplicate key update” is slower than “replace into”.
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.
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.
Well, this is old. But of course you only need to provide a value once, there's no reason to add it a second time in the query (which comes in handy for multiple inserts, or prepared statements):
INSERT INTO table1 (col1, col2, col3, col4) VALUES ('val1', 'val2', 'val3', 'val4') ON DUPLICATE KEY UPDATE col2=VALUES(col2), col3=VALUES(col3) [,...] Which has as advantage it will still work for a multiple insert statement:
INSERT INTO table1 (col1, col2, col3, col4) VALUES ('val1', 'val2', 'val3', 'val4'), ('val5', 'val6', 'val7', 'val8'), ('val9', 'val10', 'val11', 'val12') ON DUPLICATE KEY UPDATE col2=VALUES(col2), col3=VALUES(col3) [,...] 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
