mysql - 在同一查询中对 dup 进行 INSERT INTO、SELECT 和 UPDATE

Question

我正在尝试使用插入和选择更新重复键。

INSERT INTO f_s.s_d_p(dom,title,lastupdated)
SELECT dom AS dDom,
COUNT(db2.rets.dom) AS dcount,
NOW() AS timeNow
FROM db2.rets
GROUP BY db2.rets.dom
LIMIT 5
ON DUPLICATE KEY UPDATE title = dDom, last_updated = NOW();

但是当我运行查询时，我总是得到“未知列 dDom”

我尝试为选择查询设置别名，但没有成功。

Answer 1

使用字段名称，而不是别名:ON DUPLICATE KEY UPDATE title = db2.rets.dom, last_updated = NOW(); 。或者做

INSERT INTO f_s.s_d_p(dom,title,lastupdated)
SELECT * FROM (
SELECT dom AS dDom,
COUNT(db2.rets.dom) AS dcount,
NOW() AS timeNow
FROM db2.rets
GROUP BY db2.rets.dom
LIMIT 5)
a
ON DUPLICATE KEY UPDATE title = a.dDom, last_updated = NOW();