mysql - 在重复键更新中使用不同列插入选择

Question

INSERT INTO options (owner, name, value, modified)
SELECT owner, name, value, modified, @draft:=draft FROM
(
    ...
) `options`
ON DUPLICATE KEY UPDATE value=VALUES(value), modified=@draft

上面会出现列数与行数不匹配的错误。

有没有办法可以将一列选择到@draft中，而不将其包含在插入值中，但可以在重复键更新中使用？

Answer 1

如 the manual 中所述:

In the values part of ON DUPLICATE KEY UPDATE, you can refer to columns in other tables, as long as you do not use GROUP BY in the SELECT part. One side effect is that you must qualify nonunique column names in the values part.

因此，你可以这样做:

INSERT INTO options (owner, name, value, modified)
  SELECT owner, name, value, modified FROM ( ... ) options2
ON DUPLICATE KEY UPDATE value=VALUES(value), modified=options2.draft

查看 sqlfiddle .