Menu
I am trying to update a LARGE MyISAM table (25 million records) using a CLI script. The table is not being locked/used by anything else.
I figured instead of doing single UPDATE queries for each record, I might as well utilize the CASE feature.
The id field is PRIMARY. I suspect the following query should take milliseconds.
UPDATE `table` SET `uid` = CASE WHEN id = 1 THEN 2952 WHEN id = 2 THEN 4925 WHEN id = 3 THEN 1592 END Lo and behold, the query hogs the CPU and doesn't finish in forever.
Then, to my surprise, I found out that the query is updating all the 25 million rows, placing a NULL on rows that I didn't specify.
What is the purpose of that? Can I just do a MASS update on specific rows without updating 25 million rows every time I execute this query? Or do I have to do individual updates and then commit?
678
#3) With UPDATE StatementsMySQL CASE can also be used while updating an existing column in the table. Let's try to understand this with the help of an example with the test data we have. We can use the below query to achieve such updates without having to write UPDATE queries to have multiple WHERE or IF clauses.
MySQL UPDATE command can be used to update multiple columns by specifying a comma separated list of column_name = new_value. Where column_name is the name of the column to be updated and new_value is the new value with which the column will be updated.
Definition and UsageThe CASE statement goes through conditions and return a value when the first condition is met (like an IF-THEN-ELSE statement). So, once a condition is true, it will stop reading and return the result. If no conditions are true, it will return the value in the ELSE clause.
Try this
UPDATE `table` SET `uid` = CASE WHEN id = 1 THEN 2952 WHEN id = 2 THEN 4925 WHEN id = 3 THEN 1592 ELSE `uid` END WHERE id in (1,2,3) If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
