我正在執(zhí)行插入查詢，其中如果已經存在唯一鍵，則許多列中的大多數都需要更新為新值。它是這樣的：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=xON DUPLICATE KEY UPDATE ...; //update all fields to values from SELECT, // except for exp_id, created_by, location, animal, // starttime, endtime我不確定該UPDATE子句的語法應該是什么。如何從SELECT子句引用當前行？
2 回答
開滿天機
TA貢獻1786條經驗 獲得超13個贊
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, ...
添加回答
舉報
0/150
提交
取消