mysql - 插入……选择……重复键更新

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

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子句引用当前行？

最佳答案

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

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