INSERT INTO SELECT FROM ON DUPLICATE KEY UPDATE

Question

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

User · Answer

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, ...

User · Answer

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

[mysql] INSERT INTO ... SELECT FROM ... ON DUPLICATE KEY UPDATE

Examples related to mysql

Examples related to insert-update