If you are familiar with the MySQL INSERT syntax you’ve probably already noticed the ability to insert multiple rows with one call:
[sourcecode lang=”sql”]
INSERT INTO table_name (a,b,c) VALUES (1,2,3), (4,5,6), (7,8,9);
[/sourcecode]
This can be handy if you have several rows that you need to insert, no point in making separate calls for each one.
Another nice feature is the ON DUPLICATE syntax, which allows for:
[sourcecode lang=”sql”]
INSERT INTO table_name (a,b,c) VALUES (1,2,3)
ON DUPLICATE KEY UPDATE c = c + 1;
[/sourcecode]
If you have a unique index on the table, then ON DUPLICATE will kick in if there is a conflict with the unique on insert. Without ON DUPLICATE you’d generally do a SELECT first to see if the value was already in the table, then either do an INSERT or an UPDATE based on the response.
What you may not have noticed is that you can combine the multi-row insert and ON DUPLICATE features to do:
[sourcecode lang=”sql”]
INSERT INTO table_name (a,b,c) VALUES (1,2,3), (4,5,6), (7,8,9)
ON DUPLICATE KEY UPDATE c = c + 1;
[/sourcecode]
Where might you use something like this? If you were looping over data and inserting using ON DUPLICATE inside the loop you could instead build the SQL string inside the loop and then issue a single database request after the loop.
Exactly what sort of savings you’ll see depends on a number of factors ( number of iterations in your loop, latency for database requests, etc. ), so run your own tests to determine if this will help for your particular situation.
8 replies on “MySQL: INSERT ON DUPLICATE and Multiple Rows”
Glad I remembered you posted this, gonna use this in a few minutes 🙂
Nice, let me know if it using this method helped.
You can also use this syntax to update many existing rows in a table using a single query.
Example:
INSERT INTO categories
(id, display_order, title)
VALUES
(1, 3, ‘New Title 1’),
(2, 4, ‘New Title 2’),
(3, 5, ‘New Title 3’)
ON DUPLICATE KEY UPDATE
display_order=VALUES(display_order),
title=VALUES(title);
OPTIMIZE TABLE tbl_name;
The advantage of this solution is that it’s driven by the indexing of the primary keys, on which a CASE block, the popular solution for updating multiple rows with different values, cannot rely. The OPTIMIZE TABLE part is only advantageous when updating variable-length cells (i.e. VARCHAR, VARBINARY, BLOB, or TEXT). For multiple-column primary keys, you simply specify all of them in the statement.
I use ON DUPLICATE KEY UPDATE to update more than three column in the same query but it’s give me error how can i correct Thanks!
Fantastic!
Multirow inserts make scripts so much faster, nice to see you can combine them with the handy “on duplicate” function!
I have seen many examples of this, but none deal with an INSERT/SELECT and multi-column update from that select. The requirement is to transfer rows from table A to table B and update existing record that have a matching key…
INSERT INTO TABLEB (COL1,COL2,COL3,COL4)
SELECT COL1,COL2,COL3,COL4 FROM TABLEA
ON DUPLICATE KEY UPDATE
SET … (what syntax to update all [non-key?] columns with the selected data?)
Do I need aliases to identify the tables in the SET? (SET b.COL1 = a.COL1) of is there a syntax where the SELECT follows the entire INSERT/ON DUPLICATE KEY part?
Thanks.
That is a different challenge from the one I talked about in the post, and one I haven’t specifically looked at.
c=c+1;
please where is the c = c+1; in my query
$sql = mysqli_query($dbCon,”INSERT INTO personal_chat(id,reciever, sender, sender_phone, message) VALUES(‘$id’,’$reciever’,’$sender’, ‘$sender_phone’, ‘$message’) ON DUPLICATE KEY UPDATE reciever=’$reciever’, sender=’$sender’, sender_phone=’$sender_phone’, message=’$message'”);