Search code examples
mysqlsqlmariadbmariasql

On Duplicate Key update - MariaDB

I have a MySQL Statement to insert data to 4 rows at a time. The insert is working but I'm having difficulty with the ON DUPLICATE KEY UPDATE.

I'm getting an error: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near ''count = VALUES(11, 22, 33, 44)'' at line 15

Here is an example:

INSERT INTO table1 (id, dept, date, count)
VALUES
(1, 4, 2018-01-15, 3),
(2, 3, 2018-01-15, 4),
(3, 3, 2018-01-15, 14),
(4, 2, 2018-01-15, 11)
ON DUPLICATE KEY UPDATE
count = VALUES(11, 22, 33, 44)

I tried to wraps the dept and count update with '' but that didn't help. Is there a better way to update the count on DUPLICATES. Could you please help! Thanks!

Solution

  • The argument to VALUES() is supposed to be the name of a column being inserted into. It will use the value that would have been inserted into that column if there hadn't been a duplication.

    INSERT INTO table1 (id, dept, date, count)
VALUES
(1, 4, 2018-01-15, 3),
(2, 3, 2018-01-15, 4),
(3, 3, 2018-01-15, 14),
(4, 2, 2018-01-15, 11)
ON DUPLICATE KEY UPDATE
count = VALUES(count)

    If id = 1 already exists, this will set its count to 3 and leave all the other columns unchanged.