Bulk insert and update in mysql

Question

I am trying to make some bulk insert in my table but it should cater for duplicate values. If there are duplicate then it should update the rows instead of updating it.

My table is: user(id, name, url) where id is PK

I tried this:

INSERT INTO `user` (`id`, `name`, `url`) 
    VALUES 
    ('7656', 'Tom', 'http://user.com/7656'),
    ('1234', 'Jean', 'http://user.com/1234'), 
    ('8596', 'Pierre', 'http://user.com/8596'),         
    ('2035', 'Somon', 'http://user.com/2035'), 
    ('3685', 'Lola', 'http://user.com/3685')
    ON DUPLICATE KEY UPDATE name=VALUES(name), url=VALUES(url)

Assume that all the 5 record that i am trying to insert already exist in my table with just the url to update, how should i do it?

Is it possible to update rows with bulk insert like this?

Why not try it and see what happens? – Pekka Sep 25 '15 at 07:35 — Pekka, Sep 25 '15 at 07:35

Puya Sarmidani · Answer 1 · 2015-09-25T08:40:15.357

1

INSERT INTO `table` (a, b, c) 
VALUES (?, ?, ?), 
VALUES (?, ?, ?), 
VALUES (?, ?, ?)
ON DUPLICATE KEY UPDATE a = VALUES(a), b = VALUES (b), c = VALUES(c)

you need to set the VALUES everytime.

Also the way you've build the query is also OK but you have a ' , ' extra in the first value ('7656', 'Tom', 'http://user.com/7656' , ),

edited Sep 25 '15 at 08:40

answered Sep 25 '15 at 07:37

Puya Sarmidani

1,226
9
26

Its working, but for some reason i am updating 5 records with only 1 colum which is being changed and it tells me 10 row(s) affected. Any idea? – user2707590 Sep 25 '15 at 07:56
Can you edit your question above with the new query. I need to see how its build now – Puya Sarmidani Sep 25 '15 at 08:18
It's almost the same, i just replaced AND by , on the update part. see above – user2707590 Sep 25 '15 at 10:33
You have a ' , ' extra in the first value ('7656', 'Tom', 'http://user.com/7656' , ), – Puya Sarmidani Sep 25 '15 at 10:47
yeah, this is a typing mistake, should be removed from there.. but this is not the thing whichh is making 10 rows affected – user2707590 Sep 25 '15 at 11:03
Its because you update on duplicate key. So you got 5 records but you update 2 values ( name and url ) so 5 names will be updated and 5 url records. – Puya Sarmidani Sep 25 '15 at 11:16
With ON DUPLICATE KEY UPDATE, the affected-rows value per row is 1 if the row is inserted as a new row and 2 if an existing row is updated. – Puya Sarmidani Sep 25 '15 at 11:18

Bulk insert and update in mysql

1 Answers1