INSERT INTO ... SELECT FROM ... ON DUPLICATE KEY UPDATE
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?
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, ...
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, ...
when SELECT statement has a GROUP BY clause.
....
ON DUPLICATE KEY UPDATE
larct=VALUES(larct), lardur=VALUES(lardur),lardist=
VALUES(lardist)
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.
Follow WeChat
Success story sharing
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.