152

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?

Improve this question

4 Answers 4

Reset to default
220

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, ...
Improve this answer
Sign up to request clarification or add additional context in comments.

13 Comments

@dnagirl: TIP: don't try to update any of the PK columns, only those that need updating go into the list
Your suggested syntax works and the t. is required. I also found an article on xaprb (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.
Note: This will not work when SELECT statement has a GROUP BY clause
@john What to do if SELECT statement has a GROUP BY clause
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.`
|
69

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, ...
Improve this answer

4 Comments

This is the right answer for queries with COUNT, GROUP etc.. Thanks.
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.
You could also use field=VALUES(field) in the duplicate update as in answer in stackoverflow.com/questions/16935896/…
Perfect! Thank you! Regular answer is useful as well, but dosnt work for group by... This one does. Save my time and my life :)
3

when SELECT statement has a GROUP BY clause.

    ....
    ON DUPLICATE KEY UPDATE    
    larct=VALUES(larct), lardur=VALUES(lardur),lardist= 
    VALUES(lardist)
Improve this answer

1 Comment

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
2

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.

Improve this answer

1 Comment

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.