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?

eggyal · Accepted Answer · 2012-06-22 10:33:53Z

3

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.

answered Jun 22, 2012 at 10:33

eggyal

127k18 gold badges220 silver badges244 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

insert into select using different column in duplicate key update

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related