How to fix VALUES() deprecation notice in INSERT, ON DUPLICATE KEY UPDATE in MYSQL

Question

VALUES() gives a deprecation notice in mysql 8.0 when used in a query like this

INSERT INTO foo (bar, baz) VALUES (1,2)
ON DUPLICATE KEY UPDATE baz=VALUES(baz)

This post showed how to fix it if you are updating a single row, but not a solution to multiple rows, which is what we use VALUES() for. Mysql 'VALUES function' is deprecated From that post it says

INSERT INTO foo (bar, baz) VALUES (1,2)
ON DUPLICATE KEY UPDATE baz=VALUES(baz)

to

INSERT INTO foo (bar, baz) VALUES (1,2) AS new_foo
ON DUPLICATE KEY UPDATE baz=new_foo.baz

This is okay for single insert queries but I don’t know how to scale it for multiple inserts

INSERT INTO foo (bar, baz) 
VALUES (1,2) AS new_foo1,
VALUES (3,4) AS new_foo2,
VALUES (5,6) AS new_foo3
ON DUPLICATE KEY UPDATE baz=new_foo.baz  #what to do here??

This looks excessive when inserting multiplerowsand not a replacement for the ease of VALUES(). Can someone tell me the correct way to fix the deprecation of VALUES() in this kind of query?

Answer 1

This is in the manual. You write values once, then all the tuples, then as followed by the alias.

https://dev.mysql.com/doc/refman/8.0/en/insert-on-duplicate.html

INSERT INTO t1 (a,b,c) VALUES (1,2,3),(4,5,6) AS new
 ON DUPLICATE KEY UPDATE c = new.a+new.b;

This creates one alias to reference each row successively. Similar to a correlation name in JOIN syntax.