MySQL ON DUPLICATE KEY UPDATE for multiple rows insert in single query
Solution 1
Beginning with MySQL 8.0.19 you can use an alias for that row (see reference).
INSERT INTO beautiful (name, age)
VALUES
('Helen', 24),
('Katrina', 21),
('Samia', 22),
('Hui Ling', 25),
('Yumie', 29)
AS new
ON DUPLICATE KEY UPDATE
age = new.age
...
For earlier versions use the keyword VALUES
(see reference, deprecated with MySQL 8.0.20).
INSERT INTO beautiful (name, age)
VALUES
('Helen', 24),
('Katrina', 21),
('Samia', 22),
('Hui Ling', 25),
('Yumie', 29)
ON DUPLICATE KEY UPDATE
age = VALUES(age),
...
Solution 2
INSERT INTO ... ON DUPLICATE KEY UPDATE will only work for MYSQL, not for SQL Server.
for SQL server, the way to work around this is to first declare a temp table, insert value to that temp table, and then use MERGE
Like this:
declare @Source table
(
name varchar(30),
age decimal(23,0)
)
insert into @Source VALUES
('Helen', 24),
('Katrina', 21),
('Samia', 22),
('Hui Ling', 25),
('Yumie', 29);
MERGE beautiful AS Tg
using @source as Sc
on tg.namet=sc.name
when matched then update
set tg.age=sc.age
when not matched then
insert (name, age) VALUES
(SC.name, sc.age);
Related videos on Youtube
Prashant
I am a Co-Founder at RadicalLoop Technolabs, and a full stack Web Developer, primarily working with Laravel, Zend Framework, CakePHP, AngularJS and nodeJS.
Updated on June 22, 2021Comments
-
Prashant almost 3 years
I have a SQL query where I want to insert multiple rows in single query. so I used something like:
$sql = "INSERT INTO beautiful (name, age) VALUES ('Helen', 24), ('Katrina', 21), ('Samia', 22), ('Hui Ling', 25), ('Yumie', 29)"; mysql_query( $sql, $conn );
The problem is when I execute this query, I want to check whether a
UNIQUE
key (which is not thePRIMARY KEY
), e.g.'name'
above, should be checked and if such a'name'
already exists, the corresponding whole row should be updated otherwise inserted.For instance, in the example below, if
'Katrina'
is already present in the database, the whole row, irrespective of the number of fields, should be updated. Again if'Samia'
is not present, the row should be inserted.I thought of using:
INSERT INTO beautiful (name, age) VALUES ('Helen', 24), ('Katrina', 21), ('Samia', 22), ('Hui Ling', 25), ('Yumie', 29) ON DUPLICATE KEY UPDATE
Here is the trap. I got stuck and confused about how to proceed. I have multiple rows to insert/update at a time. Please give me a direction. Thanks.
-
Prashant about 14 yearsYes but using REPLACE the old row is deleted before the new row is inserted. I want to retain the old row for maintaining primary ids.
-
a1ex07 about 14 yearsI didn't realize that retaining the old PK is somehow important to you... Surely, REPLACE won't work in this case...
-
MLeFevre over 9 yearsIf you don't care about PK however, like in my case, this works perfectly.
-
Oliver M Grech over 9 yearsPS: "replace into" requires both delete and insert privileges!
-
Ross over 9 yearsand for the above mentioned delete/insert reasons, its also much slower in these instances.
-
imVJ over 7 yearsWhat if you want to update few columns? Replace will update all.
-
phoenix almost 4 yearsGreat suggestion. If you want to increment a value when duplicate, add this after the "ON DUPLICATE KEY UPDATE" part: total = total + VALUES( total )
-
Simon over 3 yearsCaution:
The use of VALUES() to refer to the new row and columns is deprecated beginning with MySQL 8.0.20
. See : dev.mysql.com/doc/refman/8.0/en/insert-on-duplicate.html -
Brandon over 2 yearsOriginal question is specifically about MySQL
-
harlekintiger over 2 yearsCaution: Don't use the variable name
new
-
Peter Lang over 2 years@harlekintiger They seem to use
new
in the manual that I linked. I don't work with MySQL though, so could you please elaborate why it should not be namednew
? Thank you! -
harlekintiger over 2 years@PeterLang of course, I should've specified the first time. While it does work and won't throw an error, at least for now, in MySQL 'new' is a reserved keyword MySQL 8.0 Manual: 9.3 Keywords and Reserved Words