insert into select using different column in duplicate key update

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

Above will error with column count doesn't match row count.

Is there a way I can SELECT a column into @draft without it being included as part of the inserts values but so it's usable in the DUPLICATE KEY UPDATE?

Answer 1

As stated in 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.

Therefore, you could do:

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

See it on sqlfiddle.