2

In the following query, oversimplified from my actual scenario but close enough:

INSERT INTO foo(bar_id, baz)
SELECT
    id,
    5 AS new_baz
FROM
    bar
ON DUPLICATE KEY UPDATE baz=new_baz

I get an Unknown column 'new_baz' in 'field list' error. However, if I change the last line to contain baz=bar.id instead then it all works, so it seems like it's an issue with the fact that new_baz is not actually from a column in a table, but a literal value.

How can I use the value of new_baz in the last ON DUPLICATE KEY UPDATE line?

8
  • 3
    ON DUPLICATE KEY UPDATE baz = VALUES(baz) Commented Feb 13, 2020 at 10:19
  • 1
    You could use ON DUPLICATE KEY UPDATE baz = 5 of course but the above syntax is more versatile. Commented Feb 13, 2020 at 10:21
  • @ypercubeᵀᴹ Thanks, is that meant to be VALUES(new_baz)? I don't see where new_baz even comes into it otherwise. Assuming so, that still gives the same error unfortunately.
    – cb7
    Commented Feb 13, 2020 at 10:23
  • 2
    See VALUES() function. is that meant to be VALUES(new_baz)? NO.
    – Akina
    Commented Feb 13, 2020 at 10:29
  • 1
    @ArunSR see my complete answer. What did you try and what error did you get? Commented Nov 20, 2022 at 17:23

1 Answer 1

2

It should be: ON DUPLICATE KEY UPDATE baz = VALUES(baz)

When the query finds a duplicate and the ON DUPLICATE KEY UPDATE clause is activated, we can use the VALUES(x) syntax to get the values that were to be inserted in the x column. The name used (new_baz) in the select query is irrelevant and can't be used there.

For more details, see MySQL's documentation about the VALUES() function.

Complete query:

INSERT INTO foo 
   (bar_id, baz)
SELECT
    id, 5 AS new_baz
FROM
    bar
ON DUPLICATE KEY UPDATE 
    baz = VALUES(baz) ;

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.