MySQL INSERT INTO ... SELECT FROM ... ON DUPLICATE KEY UPDATE
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/2472229/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
INSERT INTO ... SELECT FROM ... ON DUPLICATE KEY UPDATE
提问by dnagirl
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
I'm not sure what the syntax for the UPDATEclause should be. How do I refer to the current row from the SELECTclause?
我不确定该UPDATE子句的语法应该是什么。如何从SELECT子句中引用当前行？
回答by Marcus Adams
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.
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, ...
回答by iCurious
Although I am very late to this but after seeing some legitimate questions for those who wanted to use INSERT-SELECTquery with GROUP BYclause, I came up with the work around for this.
虽然我对此很晚，但在看到那些想要使用INSERT-SELECTquery withGROUP BY子句的人的一些合法问题后，我想出了解决这个问题的方法。
Taking further the answer of Marcus Adamsand accounting GROUP BYin it, this is how I would solve the problem by using Subqueries in the FROM Clause
进一步参考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, ...
