how to convert multiple update query in one query in mysql

Question

I have to update different rows I have written below queries but it is fine with few records but what will the way to do this is just one query. I have other similar question but did not find matching case with my scenario.

UPDATE sample_topup SET meta_value='topup'  WHERE meta_key='status' ;
UPDATE sample_topup SET meta_value='Topup'  WHERE meta_key='status_label' ;
UPDATE sample_topup SET meta_value='tage-1'  WHERE meta_key='stage' ;
UPDATE sample_topup SET meta_value='Stage 1'  WHERE meta_key='lapp_stage_label' ;

I want the solution which gives the faster and smooth execution because in my table there are more than 10,000,000 records.

Possible duplicate of Multiple Updates in MySQL

Onkar Musale
– Onkar Musale

2019-04-12 11:09:03 +00:00
Commented Apr 12, 2019 at 11:09 — Onkar Musale
– Onkar Musale, Commented Apr 12, 2019 at 11:09

sticky bit · Accepted Answer · 2019-04-12 11:56:11Z

1

You can use a CASE expression checking for the meta_key being one the ones where you want to update a row. If it matches it returns the right value for it. Otherwise jsut meta_value, i.e. not changing meta_value.

UPDATE sample_topup
       SET meta_value = CASE
                          WHEN meta_key = 'status' THEN
                            'topup'
                          WHEN meta_key = 'status_label' THEN
                            'Topup'
                          WHEN meta_key = 'stage' THEN
                            'tage-1'
                          WHEN meta_key = 'lapp_stage_label' THEN
                            'Stage 1'
                          ELSE
                            meta_value
                        END
       WHERE meta_key IN ('status',
                          'status_label',
                          'stage',
                          'lapp_stage_label');

edited Apr 12, 2019 at 11:56

answered Apr 12, 2019 at 11:11

sticky bit

37.7k12 gold badges34 silver badges46 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Raymond Nijland Over a year ago

You sure If MySQL optimizer would or can use indexes with this query? Because the ELSE meta_value part would suggest the MySQL optimizer to table scan right because it's a defualt value for non matching values for the complete table, iám not sure how MySQL optimizer would handle this query sometimes the MySQL optimizer does the things a bit wierd? i think adding WHERE meta_key IN('status', 'status_label', 'stage', 'lapp_stage_label') would result in better results when MySQL optimizer will or will not use the index.

sticky bit Over a year ago

@RaymondNijland: Thanks for the comment. Adding an WHERE ... IN (...) is indeed a good idea, I missed that. But still I think that four query searching for an exact value may be faster than the one with an IN, which makes traversing the index a little more complicated. But frankly, I don't know if the MySQL engine maybe can handle that pretty well too. So I leave it as a warning not a definite statement.

Raymond Nijland Over a year ago

"But still I think that four query searching for an exact value may be faster than the one with an IN," Well four separted UPDATES could mean four possible index updates also, one query would be faster (in InnoDB) for sure because of the auto commit but running in a transaction most likely the four separated would gain the same or near performance in thoery as your rewrite as the possible index update will be done in one go in a transaction or single query.

sticky bit Over a year ago

Hmm, OK some valid points. I edited it out. Should the OP encounter issues with it they might come back here anyway.

Collectives™ on Stack Overflow

how to convert multiple update query in one query in mysql

1 Answer 1

4 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related