0

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.

vaibhav kulkarni
  • 1,733
  • 14
  • 20
  • 1
    Possible duplicate of [Multiple Updates in MySQL](https://stackoverflow.com/questions/3432/multiple-updates-in-mysql) – Onkar Musale Apr 12 '19 at 11:09

1 Answers1

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');
sticky bit
  • 36,626
  • 12
  • 31
  • 42
  • 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. – Raymond Nijland Apr 12 '19 at 11:21
  • 1
    @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. – sticky bit Apr 12 '19 at 11:29
  • *"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. – Raymond Nijland Apr 12 '19 at 11:54
  • 1
    Hmm, OK some valid points. I edited it out. *Should* the OP encounter issues with it they might come back here anyway. – sticky bit Apr 12 '19 at 11:58