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?