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 · Accepted 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

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

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

Examples related to mysql

Examples related to insert-update