INSERT INTO ... SELECT FROM ... ON DUPLICATE KEY UPDATE
Asked 13 years, 8 months ago
Modified 1 year, 11 months ago
Viewed 174k times
Report this ad
149
I'm doing an insert query where most of many columns would need to be updated to the new values if a unique key already existed. It goes something like this:
INSERT INTO lee(exp_id, created_by,
location, animal,
starttime, endtime, entct,
inact, inadur, inadist,
smlct, smldur, smldist,
larct, lardur, lardist,
emptyct, emptydur)
SELECT id, uid, t.location, t.animal, t.starttime, t.endtime, t.entct,
t.inact, t.inadur, t.inadist,
t.smlct, t.smldur, t.smldist,
t.larct, t.lardur, t.lardist,
t.emptyct, t.emptydur
FROM tmp t WHERE uid=x
ON DUPLICATE KEY UPDATE ...;
//update all fields to values from SELECT,
// except for exp_id, created_by, location, animal,
// starttime, endtime
I'm not sure what the syntax for the UPDATE clause should be. How do I refer to the current row from the SELECT clause?
mysqlinsert-update
Share
Improve this question
Follow
edited Mar 18, 2010 at 18:19
lexu's user avatar
lexu
8,78655 gold badges4646 silver badges6363 bronze badges
asked Mar 18, 2010 at 17:57
dnagirl's user avatar
dnagirl
20.3k1313 gold badges8383 silver badges123123 bronze badges
Add a comment
4 Answers
Sorted by:
217
MySQL will assume the part before the equals references the columns named in the INSERT INTO clause, and the second part references the SELECT columns.
INSERT INTO lee(exp_id, created_by, location, animal, starttime, endtime, entct,
inact, inadur, inadist,
smlct, smldur, smldist,
larct, lardur, lardist,
emptyct, emptydur)
SELECT id, uid, t.location, t.animal, t.starttime, t.endtime, t.entct,
t.inact, t.inadur, t.inadist,
t.smlct, t.smldur, t.smldist,
t.larct, t.lardur, t.lardist,
t.emptyct, t.emptydur
FROM tmp t WHERE uid=x
ON DUPLICATE KEY UPDATE entct=t.entct, inact=t.inact, ...
Share
Improve this answer
Follow
edited Mar 18, 2010 at 19:16
answered Mar 18, 2010 at 18:15
Marcus Adams's user avatar
Marcus Adams
53.2k99 gold badges9393 silver badges143143 bronze badges
7
@dnagirl: TIP: don't try to update any of the PK columns, only those that need updating go into the list –
lexu
Mar 18, 2010 at 18:18
50
Your suggested syntax works and the t. is required. I also found an article on xaprb (xaprb.com/blog/2006/02/21/flexible-insert-and-update-in-mysql) that uses this syntax: on duplicate key update b = values(b), c = values(c). This also works. –
dnagirl
Mar 18, 2010 at 18:43
9
Note: This will not work when SELECT statement has a GROUP BY clause –
joHN
Jul 30, 2014 at 11:44
12
@john What to do if SELECT statement has a GROUP BY clause –
Kathir
Aug 26, 2014 at 13:15
3
dev.mysql.com/doc/refman/5.6/en/insert-on-duplicate.html says ` in MySQL 5.6.4 and later, INSERT ... SELECT ON DUPLICATE KEY UPDATE statements are flagged as unsafe for statement-based replication... In addition, beginning with MySQL 5.6.6, an INSERT ... ON DUPLICATE KEY UPDATE statement against a table having more than one unique or primary key is also marked as unsafe.` –
Abdul Muneer
Aug 10, 2015 at 5:53
Show 8 more comments
Report this ad
66
Although I am very late to this but after seeing some legitimate questions for those who wanted to use INSERT-SELECT query with GROUP BY clause, I came up with the work around for this.
Taking further the answer of Marcus Adams and accounting GROUP BY in it, this is how I would solve the problem by using Subqueries in the FROM Clause
INSERT INTO lee(exp_id, created_by, location, animal, starttime, endtime, entct,
inact, inadur, inadist,
smlct, smldur, smldist,
larct, lardur, lardist,
emptyct, emptydur)
SELECT sb.id, uid, sb.location, sb.animal, sb.starttime, sb.endtime, sb.entct,
sb.inact, sb.inadur, sb.inadist,
sb.smlct, sb.smldur, sb.smldist,
sb.larct, sb.lardur, sb.lardist,
sb.emptyct, sb.emptydur
FROM
(SELECT id, uid, location, animal, starttime, endtime, entct,
inact, inadur, inadist,
smlct, smldur, smldist,
larct, lardur, lardist,
emptyct, emptydur
FROM tmp WHERE uid=x
GROUP BY location) as sb
ON DUPLICATE KEY UPDATE entct=sb.entct, inact=sb.inact, ...
Share
Improve this answer
Follow
answered Apr 16, 2015 at 8:08
iCurious's user avatar
iCurious
8,22177 gold badges2828 silver badges4646 bronze badges
10
This is the right answer for queries with COUNT, GROUP etc.. Thanks. –
Kostanos
Jun 26, 2015 at 16:28
1
Thank you so much, dude! I really liked this approach, specially because it allows me to create a mechanism like Continuous Query, something that InfluxDB's does. –
Miere
Jun 15, 2016 at 17:12
1
You could also use field=VALUES(field) in the duplicate update as in answer in stackoverflow.com/questions/16935896/… –
Erik Melkersson
Sep 16, 2018 at 7:24
Perfect! Thank you! Regular answer is useful as well, but dosnt work for group by... This one does. Save my time and my life :) –
Pavel
Nov 1 at 9:22
Add a comment
1
when SELECT statement has a GROUP BY clause.
....
ON DUPLICATE KEY UPDATE
larct=VALUES(larct), lardur=VALUES(lardur),lardist=
VALUES(lardist)
Share
Improve this answer
Follow
edited Jun 2, 2021 at 10:56
answered Jun 2, 2021 at 10:50
Vũ Anh Tuấn's user avatar
Vũ Anh Tuấn
1122 bronze badges
Note DBeaver Outputted this message when I used VALUES() 'VALUES function' is deprecated and will be removed in a future release. Please use an alias (INSERT INTO ... VALUES (...) AS alias) and replace VALUES(col) in the ON DUPLICATE KEY UPDATE clause with alias.col instead –
user716255
May 5 at 15:45
Add a comment
Report this ad
1
ON DUPLICATE KEY UPDATE a=VALUES(a), b=VALUES(b) will not work when fields' name are different e.g. ON DUPLICATE KEY UPDATE a=VALUES(c), b=VALUES(c) sometimes fails, but ON DUPLICATE KEY UPDATE a=t.c, b=t.c... works.
|