148

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?

lexu
  • 8,766
  • 5
  • 45
  • 63
dnagirl
  • 20,196
  • 13
  • 80
  • 123

4 Answers4

216

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, ...
Marcus Adams
  • 53,009
  • 9
  • 91
  • 143
  • 7
    @dnagirl: **TIP:** don't try to update any of the PK columns, only those that need updating go into the list – lexu Mar 18 '10 at 18:18
  • 49
    Your suggested syntax works and the `t.` is required. I also found an article on xaprb (http://www.xaprb.com/blog/2006/02/21/flexible-insert-and-update-in-mysql/) that uses this syntax: `on duplicate key update b = values(b), c = values(c)`. This also works. – dnagirl Mar 18 '10 at 18:43
  • 9
    Note: This will not work when SELECT statement has a GROUP BY clause – joHN Jul 30 '14 at 11:44
  • 12
    @john What to do if SELECT statement has a GROUP BY clause – Kathir Aug 26 '14 at 13:15
  • Awesome! But I guess there no syntax to update all columns `ON DUPLICATE KEY` ? – Nic Cottrell Sep 25 '14 at 10:19
  • 3
    http://dev.mysql.com/doc/refman/5.6/en/insert-on-duplicate.html says ` in MySQL 5.6.4 and later, INSERT ... SELECT ON DUPLICATE KEY UPDATE statements are flagged as unsafe for statement-based replication... In addition, beginning with MySQL 5.6.6, an INSERT ... ON DUPLICATE KEY UPDATE statement against a table having more than one unique or primary key is also marked as unsafe.` – Abdul Muneer Aug 10 '15 at 05:53
  • 1
    If the SELECT returns zero results - the insert/update will timeout... and lock --- not good! looking for a workaround – PodTech.io May 03 '16 at 10:31
  • Hello friends, I have a table with 7 columns, the primary key does not work for me very much for this query, since it is based on columns and values, I want to insert or update a single column from a csv file; The problem is that Duplicate is not used correctly, and if possible for this scenario: if in a row three of the columns A, B, C match their values (already exists a record) do the update; If there is no match make an insert in the queue. –  Nov 09 '16 at 21:56
  • 1
    @Hariboo: not in my experience (with MySQL 5.6 and later) that's not true. If the SELECT query returns zero rows, in an `INSERT ... SELECT ... ON DUPLICATE KEY` statement, the statement completes successfully. It's easy to run a test case that demonstrates this. If you have a statement causing a "timeout" and a "lock", the root cause is something other than the SELECT returning zero rows. (For relatively small sets returned by a SELECT, an inline view/derived table can be used as a workaround for some issues.) – spencer7593 Feb 14 '18 at 22:30
  • Can you explain how it knows what key to use? I have a slightly different situation where I am inserting from one record in the table to another record in the table and not sure how to deal with keys in this case – Joe Phillips Jun 12 '18 at 17:54
  • Not working... Its saying `column 'xyz' in field list is ambiguous` ... Even after referencing SELECT table and their columns . – santoshe61 Nov 28 '18 at 06:45
  • 1
    @Kathir, wrap your GROUP BY query in another SELECT query with alias and use it for INSERT ... SELECT ... ON DUPLICATE. – Zon May 03 '21 at 08:02
  • I have a similar query .......... insert into amalgam_care (id, name, activestatus, incorpcountry, sourcetable, sourcesystem, lastupdatedtime) select t.entityuid, t.entityname, t.activestatus, t.incorporationcountry, 'ATLAS_ENTNONSPV', 'ATLAS', t.LASTUPDATEDDATE from ATLAS_ENTNONSPV t where lower(entityIsDeleted) = 'false' ON DUPLICATE KEY UPDATE id= t.entityuid, sourcetable='ATLAS_ENTNONSPV' ........ but this returns a sql command not correctly ended error , any help appreictaed – Sithija Piyuman Thewa Hettige Apr 12 '23 at 02:42
65

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, ...
iCurious
  • 8,161
  • 7
  • 28
  • 46
  • 10
    This is the right answer for queries with COUNT, GROUP etc.. Thanks. – Kostanos Jun 26 '15 at 16:28
  • 1
    Thank you so much, dude! I really liked this approach, specially because it allows me to create a mechanism like Continuous Query, something that InfluxDB's does. – Miere Jun 15 '16 at 17:12
  • 1
    You could also use field=VALUES(field) in the duplicate update as in answer in https://stackoverflow.com/questions/16935896/mysql-on-duplicate-key-update-while-inserting-a-result-set-from-a-query – Erik Melkersson Sep 16 '18 at 07:24
1

when SELECT statement has a GROUP BY clause.

    ....
    ON DUPLICATE KEY UPDATE    
    larct=VALUES(larct), lardur=VALUES(lardur),lardist= 
    VALUES(lardist)
Vũ Anh Tuấn
  • 11
  • 2
  • Note DBeaver Outputted this message when I used VALUES() 'VALUES function' is deprecated and will be removed in a future release. Please use an alias (INSERT INTO ... VALUES (...) AS alias) and replace VALUES(col) in the ON DUPLICATE KEY UPDATE clause with alias.col instead – user716255 May 05 '23 at 15:45
0

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.

buddemat
  • 4,552
  • 14
  • 29
  • 49
yitta hhh
  • 1
  • 1
    As it’s currently written, your answer is unclear. Please [edit] to add additional details that will help others understand how this addresses the question asked. You can find more information on how to write good answers [in the help center](/help/how-to-answer). – Community Dec 15 '21 at 08:08