0

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...;
Simone Romani
  • 415
  • 2
  • 6
  • 20
  • Where does the data come from? – Paul Spiegel Aug 19 '18 at 14:21
  • 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: https://stackoverflow.com/questions/20255138/sql-update-multiple-records-in-one-query – Erubiel Aug 19 '18 at 14:32
  • The data comes from a real time API and I need to update everything beside the ID – Simone Romani Aug 19 '18 at 14:36
  • See [Why should I provide an MCVE for what seems to me to be a very simple SQL query](http://meta.stackoverflow.com/questions/333952/why-should-i-provide-an-mcve-for-what-seems-to-me-to-be-a-very-simple-sql-query) – Strawberry Aug 19 '18 at 14:55

3 Answers3

2

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.

Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
2

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;
AswathyShaji
  • 73
  • 3
  • 11
0

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

Begin Transactions;
update ...
update ...
...
update ...
//Each of normal update SQL statment
Commit Transactions;
asdf
  • 221
  • 1
  • 10