CODEWITHSUNDEEP
mysqlsqlsql-update
Simone Romani
Simone Romani

Reputation: 435

Multiple rows update into a single query

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...;

Upvotes: 0

Views: 206

Answers (3)

AswathyShaji
AswathyShaji

Reputation: 73

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;

Upvotes: 2

asdf
asdf

Reputation: 221

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

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

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269463

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.

Upvotes: 2

Related Questions