MySQL multiple upsert

Question

I have viewed many many posts regarding this and they seem to obfusticate multiple columns, also where there should be unquoted values eg:

MySQL ON DUPLICATE KEY UPDATE for multiple rows insert in single query

From my humble understand (which is wrong as there is an error) here is my full statement for a test table

insert into testDB (`UUID`,`description`,`created_at`,`updated_at`)
values ('6','test 6a',now(),now()),('7','test 7a',now(),now())
on duplicate key update
UUID = VALUES('6','7'),
description = VALUES('test 6a','test 7a'),
created_at = VALUES(now(),now()),
updated_at = VALUES(now(),now())

This is the most basic I can get the example, the column type for created_at and updated_at is obviously datetime

[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 ''6','7'), description = VALUES('test 6a','test 7a'), created_at = VALUES(now(),n' at line 1

Idiomorphic

Answer 1

The identifier in VALUE(``identifier``) is the identifier of the column (its name) corresponding to the insert statement. So it should be created_at and updated_at according to your example. The values are determined from the values of the insert branch, which lead to a (probably only first!) unique key violation.

So the update branch would be:

...
on duplicate key update
UUID = VALUES(`UUID`),
description = VALUES(`description`),
created_at = VALUES(`created_at`),
updated_at = VALUES(`updated_at`)

Tennant