1

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?

Bill Karwin
  • 538,548
  • 86
  • 673
  • 828
Hasley
  • 79
  • 6
  • 1
    exactly the same for multiple inserts: `INSERT INTO foo (bar, baz) VALUES (1,2),(3,4),(5,6) AS new_foo ON DUPLICATE KEY UPDATE baz=new_foo.baz` https://dbfiddle.uk/r5CsX0eO – ysth Nov 13 '22 at 05:08
  • 1
    That works but I didn’t expect it to. I thought the AS statement referred only to the value set it came after, but it applies to the entire set of value sets. I can see the original post has been updated to show this, too. Thank you! – Hasley Nov 13 '22 at 22:27

1 Answers1

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.

Bill Karwin
  • 538,548
  • 86
  • 673
  • 828
  • Although I looked at the manual first because I expected it to have an alternative, I did not understand its alternative because I did not know AS applied to all tuples. This is due to my newness to this. – Hasley Nov 13 '22 at 22:28