在重复的密钥更新中插入... 选择..

我正在执行一个插入查询，如果已经存在唯一键，那么大多数列都需要更新为新值。大概是这样的:

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

162472 次浏览
小开
最佳答案

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

虽然我是非常晚到这一点，但在看到一些合法的问题后，那些谁想使用 INSERT-SELECT与 GROUP BY子句查询，我想出了围绕这一点的工作。

进一步的回答 马库斯 · 亚当斯和会计 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 语句具有 GROUPBY 子句时。

    ....
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...工作。


提交答案