INSERT INTO ... SELECT FROM ... ON DUPLICATE KEY UPDATE

2021-11-20 00:00:00 mysql insert-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 将假定 equals 之前的部分引用 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, ...

相关文章