INSERT INTO ... SELECT FROM ...重复键更新 [英] INSERT INTO ... SELECT FROM ... ON DUPLICATE KEY UPDATE

问题描述

我正在执行插入查询，其中如果已经存在唯一键，则许多列中的大多数都需要更新为新值.它是这样的:

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

我不确定UPDATE子句的语法是什么.如何从SELECT子句引用当前行?

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?

推荐答案

MySQL将假定等号之前的部分引用INSERT INTO子句中命名的列，而第二部分引用SELECT列.

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

这篇关于INSERT INTO ... SELECT FROM ...重复键更新的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！