Multiple rows update into a single query

Question

I need to update over 3000 rows but it gets too slow if I run 3000+ update queries.

(This is the table I want to update)

items ( id INT(11) NOT NULL, name VARCHAR(255) NOT NULL, members_only TINYINT(1) NOT NULL, price INT(11) NOT NULL, PRIMARY KEY (id) );

So I was thinking about if there is a way to update multiple rows at once like this (since It should run faster if, in someway, I combine them all).

UPDATE items SET name='name1', members_only=1, price=20 WHERE id=1 AND SET name='name2', members_only=0, price=70 WHERE id=2 AND SET name='name3', members_only=0, price=40 WHERE id=3...;

Yeah, i don't understand if you want to write this query... or how is it that you decide which columns and values to update... but with this little info, think this might help you: stackoverflow.com/questions/20255138/… — Erubiel
– Erubiel, Commented Aug 19, 2018 at 14:32
The data comes from a real time API and I need to update everything beside the ID — Simone Romani
– Simone Romani, Commented Aug 19, 2018 at 14:36
See Why should I provide an MCVE for what seems to me to be a very simple SQL query — Strawberry
– Strawberry, Commented Aug 19, 2018 at 14:55

Gordon Linoff · Accepted Answer · 2018-08-19 14:24:40Z

I would suggest using join:

UPDATE items i JOIN (SELECT 1 as id, 'name1' as name, 1 as members_only, 20 as price UNION ALL . . . ) newdata USING (id) SET i.name = newdata.name2, i.members_only = newdata.members_only, i.price = newdata.price;

Alternatively, run separate updates for each id within the same transaction, so they all take effect at the same time.

AswathyShaji · Accepted Answer · 2020-05-21 19:22:15Z

You can use the CASE statement to update multiple rows

update items set name=(case when id='1' then 'name1' when id='2' then 'name2' when id='3' then 'name3' end), members_only=(case when id='1' then '1' when id='2' then '0' when id='3' then '0' end), price=(case when id='1' then '20' when id='2' then '70' when id='3' then '40' end) where id='1' or id='2' or id=3;

asdf · Accepted Answer · 2018-08-19 14:50:51Z

Maybe it can run more faster, by using transaction. Like this:

Begin Transactions; update ... update ... ... update ... //Each of normal update SQL statment Commit Transactions;

Collectives™ on Stack Overflow

Multiple rows update into a single query

3 Answers 3

Comments

Comments

Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Linked

Related