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.

Your first insert query is invalid. You can't union all INSERT statements. — Kamil Gosciminski
– Kamil Gosciminski, Commented Aug 7, 2018 at 7:29
@KamilG. - Sorry, my mistake. How do I edit the question for me to fix it? — DayIsGreen
– DayIsGreen, Commented Aug 7, 2018 at 7:32
What keys do you have on your table? Why use a select when a values clause would seem more appropriate? — P.Salmon
– P.Salmon, Commented Aug 7, 2018 at 7:34

Nick · Accepted Answer · 2018-08-07 08:15:06Z

4

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

edited Aug 7, 2018 at 8:15

answered Aug 7, 2018 at 7:42

Nick

147k23 gold badges67 silver badges106 bronze badges

Sign up to request clarification or add additional context in comments.

7 Comments

DayIsGreen Over a year ago

Question: What happens if the first one is a duplicate, and so is the second one, or the third one, etc.? Will they all be having similar records now?

Nick Over a year ago

Yes & no. It will cause an error, since they will end up with the same value for the key ('new'), and no inserts will happen. So trying to update with constant values can't work if you get two or more duplicate keys from the same INSERT.

DayIsGreen Over a year ago

So would that mean, this is not advisable? I was thinking, for every insert, there is a duplicate key update that would specify what the record would be if it is a duplicate. Having to set it (the duplicate key update) at the very end might cause an issue which as much as possible, I'm trying to avoid even if it is to be caught (via try-catch). Any idea how else I could do it?

DayIsGreen Over a year ago

Thanks for the update! Sorry but I don't seem to understand what you mean in your last two statements: "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". Could you please explain?

Nick Over a year ago

If you look at my new query, you'll see the UPDATE on COLUMN_1 now uses part of the old value to create the new value. This should help prevent duplicate key errors if more than one of the inserted data has a key which matches one already in the table. Since this UPDATE now refers to a column in the UNION, the rules (as pointed out in the links provided by the other answers) mean that you must use a derived table (so we use SELECT * FROM (SELECT ... UNION ... UNION ...) AS dt)

|

Tamas Rev · Accepted Answer · 2018-08-07 07:37:48Z

2

You can do it like this:

INSERT INTO tableName (COLUMN_1, COLUMN_2, COLUMN_3)
SELECT * FROM (
    SELECT 'test1', 'test2', 'test3'
    UNION ALL
    SELECT 'test4', 'test5', 'test6'
    UNION ALL
    SELECT 'test7', 'test8', 'test8') AS derived_table
ON DUPLICATE KEY UPDATE COLUMN_1='new', COLUMN_2='new', COLUMN_3='new'

Explanation:

You need to separate the SELECT and the INSERT statements. I.e. one INSERT can have only one SELECT. Then you can do UNION-s within the select.
As stated on the related manual page, INSERT INTO ... SELECT ... UNION doesn't support a ON DUPLICATE KEY UPDATE -s. However, they support it on a derived table (a.k.a. subquery).

answered Aug 7, 2018 at 7:37

Tamas Rev

7,1745 gold badges37 silver badges52 bronze badges

1 Comment

DayIsGreen Over a year ago

Question: What happens if the first one is a duplicate, and so is the second one, or the third one, etc.? Will they all be having similar records now?

Kamil Gosciminski · Accepted Answer · 2018-08-07 07:38:36Z

2

You haven't specified your primary key and unique indexes that you have, but nevertheless this will work - documentation mentions this case. Depending on your unique constraints if the row is found, all three column values will be updated (even if they are not part of the unique key).

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

To test this it's better to include some additional row since every value in your sample data is unique and you can't observe the behaviour.

answered Aug 7, 2018 at 7:38

Kamil Gosciminski

17.3k9 gold badges53 silver badges75 bronze badges

2 Comments

DayIsGreen Over a year ago

Question: What happens if the first one is a duplicate, and so is the second one, or the third one, etc.? Will they all be having similar records now?

Kamil Gosciminski Over a year ago

Answer: why don't you try it yourself? This is the best way to learn things. It's easy to set it up. I believe it should raise an error due to unique constraint violation for subsequent update.

Collectives™ on Stack Overflow

SQL Insert multiple record while using ON DUPLICATE KEY UPDATE

3 Answers 3

7 Comments

1 Comment

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

7 Comments

1 Comment

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related