CODEWITHSUNDEEP
mysqlperformancesql-update
Malibur
Malibur

Reputation: 1783

Is there a more efficient way to run multiple UPDATE sql statements

I'm a bit of a noob when it comes to sql queries, and I only really have worked with the basic SELECT, UPDATE, DELETE statements. Now I need to update a lot of rows (50+) in a vertical table, and im running the statements like this:

UPDATE `postmeta` SET meta_value = 'John' WHERE `meta_key` = 'name' AND `post_id` = 29230;
UPDATE `postmeta` SET meta_value = 'Johnson' WHERE `meta_key` = 'last_name' AND `post_id` = 29230;
UPDATE `postmeta` SET meta_value = 'www.johnsonsdiner.com' WHERE `meta_key` = 'url' AND `post_id` = 29230;
UPDATE `postmeta` SET meta_value = '[email protected]' WHERE `meta_key` = 'mail' AND `post_id` = 29230;
UPDATE `postmeta` SET meta_value = '12341234' WHERE `meta_key` = 'phone' AND `post_id` = 29230;
... + 45 more

Notice that the post_id is the same, while the meta_key differs.

is there a better way to run all these updates, that would have better performance for mySQL? would it make a difference to add a LIMIT 1 to each statement?

Any help is appreciated

Upvotes: 3

Views: 2947

Answers (2)

Oleg Komarov
Oleg Komarov

Reputation: 370

One other approach would be to use INSERT ON DUPLICATE KEY UPDATE if you know the primary key for each row you update.

So assuming (for illustration purposes) that (post_id, meta_key) is your primary key, the following query will solve your need:

INSERT INTO postmeta (post_id, meta_key, meta_value)
VALUES (29230,'name','John'), (29230,'last_name','Johnson'),...
ON DUPLICATE KEY UPDATE meta_value = VALUES(meta_value)

If the (post_id, meta_key) is not declared as a unique constraint, then you would need to first do a select to know primary key for rows you are updating, but be wary and consider possible race conditions that might apply in your situation.

Upvotes: 0

void
void

Reputation: 7880

you can use only one update with a case statement for none common conditions also bring the common conditions to where clause:

UPDATE `postmeta`
        SET meta_value = case
             when `meta_key` = 'name' then 'John'
             when `meta_key` = 'last_name' then 'Johnson'
             when `meta_key` = 'url' then 'www.johnsonsdiner.com'
             when `meta_key` = 'mail' then '[email protected]'
             when `meta_key` = 'phone' then '12341234' 
             .
             ...45 more
             .
             end
where `post_id` = 29230;

Upvotes: 5

Related Questions