INSERT INTO ... SELECT FROM ... ON DUPLICATE KEY UPDATE
Posted
by dnagirl
on Stack Overflow
See other posts from Stack Overflow
or by dnagirl
Published on 2010-03-18T17:57:56Z
Indexed on
2010/03/18
18:01 UTC
Read the original article
Hit count: 306
mysql
|insert-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?
© Stack Overflow or respective owner