SQL Insert multiple record while using ON DUPLICATE KEY UPDATE

Question

I have a query that inserts multiple data in one go as a single query.

INSERT INTO tableName (COLUMN_1, COLUMN_2, COLUMN_3) 
SELECT 'test1', 'test2', 'test3'
UNION ALL
SELECT 'test4', 'test5', 'test6'
UNION ALL
SELECT 'test7', 'test8', 'test8'

Using the above code, is there a way to implement the 'ON DUPLICATE KEY UPDATE'? Something like:

INSERT INTO tableName (COLUMN_1, COLUMN_2, COLUMN_3)
SELECT 'test1', 'test2', 'test3'
ON DUPLICATE KEY UPDATE COLUMN_1='new', COLUMN_2='new', COLUMN_3='new'
UNION ALL
SELECT 'test1', 'test2', 'test3'
ON DUPLICATE KEY UPDATE COLUMN_1='new', COLUMN_2='new', COLUMN_3='new'
UNION ALL
SELECT 'test1', 'test2', 'test3'
ON DUPLICATE KEY UPDATE COLUMN_1='new', COLUMN_2='new', COLUMN_3='new';

Note: May be irrelevant or pointless, but I'm doing this with mysql workbench.

Answer 1

Your query is basically correct, just get rid of the intermediate ON DUPLICATE KEY.... There is no need for a derived table because you are not referencing columns from the union.

INSERT INTO tableName (COLUMN_1, COLUMN_2, COLUMN_3) 
SELECT 'test8', 'test9', 'test10'
UNION ALL
SELECT 'test4', 'test5', 'test6'
UNION ALL
SELECT 'test9', 'test5', 'test6'
ON DUPLICATE KEY UPDATE COLUMN_1='new', COLUMN_2='new', COLUMN_3='new';

The problem you are going to run into is if you get more than one duplicate key on the INSERT. In that case the UPDATE will attempt to set two rows to have the same key ('new') and the INSERT will fail. You could potentially work around this by changing the query so that the UPDATE includes part of the old column value. In this case since you'll be referring to a column value you will need a derived table:

INSERT INTO tableName (COLUMN_1, COLUMN_2, COLUMN_3) 
SELECT * FROM (
    SELECT 'test8', 'test9', 'test10'
    UNION ALL
    SELECT 'test4', 'test5', 'test6'
    UNION ALL
    SELECT 'test1', 'test5', 'test6') AS dt
ON DUPLICATE KEY UPDATE COLUMN_1=CONCAT('new', COLUMN_1), COLUMN_2='new', COLUMN_3='new';

Updated SQLFiddle