0

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?

  • 2
    ON DUPLICATE KEY UPDATE baz = VALUES(baz) – ypercubeᵀᴹ Feb 13 at 10:19
  • You could use ON DUPLICATE KEY UPDATE baz = 5 of course but the above syntax is more versatile. – ypercubeᵀᴹ Feb 13 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 Feb 13 at 10:23
  • 2
    See VALUES() function. is that meant to be VALUES(new_baz)? NO. – Akina Feb 13 at 10:29

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.