Update multiple rows in MySQL v8

Question

In these 7 - 12 year old questions, the most upvoted answers advise to update multiple rows with the MySQL VALUES function:

INSERT INTO chart (id, flag)
VALUES (1, 'FLAG_1'),(2, 'FLAG_2')
ON DUPLICATE KEY UPDATE id = VALUES(id), flag = VALUES(flag);

When I tried this I with MySQL version 8.0.20 got a deprecation warning

[HY000][1287] 'VALUES function' is deprecated and will be removed in a future release. Please use an alias (INSERT INTO ... VALUES (...) AS alias) and replace VALUES(col) in the ON DUPLICATE KEY UPDATE clause with alias.col instead

The MySQL worklog advises to use an alias instead. It's also mentioned in the 'INSERT ... ON DUPLICATE KEY UPDATE Statement' docs:

INSERT INTO chart (id, flag) 
VALUES (1, 'FLAG_1'),(2, 'FLAG_2') AS aliased
ON DUPLICATE KEY UPDATE flag=aliased.flag;

However trying this new syntax I got an error:

[42000][1064] You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '' at line 3

Using the mysql command line tool I can update the table fine with new alias syntax. Why can't I do this in my usual database tool (Intellij Datagrip)?

How do I update multiple rows in MySQL v8.0.20 and later without deprecation warning and without errors?

Answer 1

The new alias syntax in the question is correct:

INSERT INTO chart (id, flag) 
VALUES (1, 'FLAG_1'),(2, 'FLAG_2') AS aliased
ON DUPLICATE KEY UPDATE flag=aliased.flag;

Also make sure to have:

• MySQL v8.0.20 or later
• Your MySQL connector driver supports features from MySQL v8.0.20 and later

The MySQL connector driver with my database tool was the culprit that raised the error. It was set to an old version.

You can update your MySQL connection driver like this:

Right click your database connection in the Database view > Data Source Properties > On the bottom it should suggest to switch your driver to version 8.0.20: