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: 279

Filed under:
|

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

Related posts about mysql

Related posts about insert-update