MariaDB query error INSERT ... ON DUPLICATE KEY

Question

When I run below query in MariaDB, I get an error:

INSERT INTO price_2 (date_1,ticker,end_price,volume)
VALUES
('2021-01-02', '000001', 1340, 1000),
('2021-01-03', '000001', 1315, 2000),
('2021-01-02', '000002', 500, 200),
('2021-01-03', '000002', 1380, 3000)
AS NEW
ON DUPLICATE KEY UPDATE
end_price = NEW.end_price, volume = NEW.volume;

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 'AS NEW ON DUPLICATE KEY UPDATE end_price = NEW.end_price, volume = NEW.volume' at line 7 */

How can I get it to run without errors?

Maybe https://stackoverflow.com/questions/9537710/is-there-a-way-to-use-on-duplicate-key-to-update-all-that-i-wanted-to-insert helps? — Nico Haase, Apr 28 '23 at 06:46
[Here](https://mariadb.com/kb/en/insert/) is the manual your error message asked you to check. Look at a form of `INSERT ... SELECT UNION ALL .. ON DUPLICATE KEY UPDATE`. — danblack, Apr 28 '23 at 07:06

score 1 · Answer 1 · answered Apr 28 '23 at 10:09

The use of an alias to refer to values being inserted in the update clause (here, NEW) is a new feature of MySQL, that is available from version 8.0.19 only. Starting version 8.0.20, the previous syntax (using the VALUES() function) is considered deprecated, and planned for removal in a future version.

But, on the other hand, this feature is not yet supported in MariaDB - hence the error that you are getting. We have to use the VALUES() function here:

INSERT INTO price_2 (date_1,ticker,end_price,volume)
VALUES 
    ('2021-01-02', '000001', 1340, 1000),
    ('2021-01-03', '000001', 1315, 2000),
    ('2021-01-02', '000002', 500, 200),
    ('2021-01-03', '000002', 1380, 3000)
ON DUPLICATE KEY UPDATE end_price = VALUES(end_price), volume = VALUES(volume)

MariaDB query error INSERT ... ON DUPLICATE KEY

1 Answers1