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.
精彩评论