INSERT INTO ... SELECT FROM ... ON DUPLICATE KEY UPDATE
我正在执行插入查询，如果已经存在唯一键，则需要将许多列中的大多数更新为新值。它是这样的：
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 将假定 equals 之前的部分引用 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, ...
虽然我对此很晚，但在看到那些想要使用带有 GROUP BY 子句的 INSERT-SELECT 查询的人的一些合理问题后，我想出了解决这个问题的方法。
进一步了解 Marcus Adams 的答案并在其中考虑 GROUP BY，这就是我使用 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, ...
当 SELECT 语句有 GROUP BY 子句时。
....
ON DUPLICATE KEY UPDATE
larct=VALUES(larct), lardur=VALUES(lardur),lardist=
VALUES(lardist)
当字段名称不同时，ON DUPLICATE KEY UPDATE a=VALUES(a), b=VALUES(b) 将不起作用，例如 ON DUPLICATE KEY UPDATE a=VALUES(c), b=VALUES(c) 有时会失败，但 ON DUPLICATE KEY UPDATE a=t.c, b=t.c... 有效。
t.是必需的。我还发现了一篇关于 xaprb (xaprb.com/blog/2006/02/21/flexible-insert-and-update-in-mysql) 的文章，它使用了这种语法：on duplicate key update b = values(b), c = values(c)。这也有效。