I have a column with codes. Now each code has been changed to something else. I am trying to update it. So I have used
SET code = CASE
WHEN code = "akdsfj" THEN "kadjsf"
WHEN code = "asdf" THEN "ndgs"
WHEN code = "hfgsd" THEN "gfdsd"
... (I am doing in batches of 1000 case statements at a time)
case statement does add time, because it is searched.
The solution? Store the pairs in a temporary table . . . with an index. So:
create temporary table code_pairs ( old_code varchar(255) not null primary key, new_code varchar(255) ); insert into code_pairs(old_code, new_code) values ('akdsfj', 'kadjsf'), ('asdf', 'ndgs'), . . . ;
update test_table tt join code_paris cp on tt.code = cp.old_code set tt.code = cp.new_code;
This saves you time because the matching code is found using the index, rather then searching one-by-one through a
case statement. In addition, no update is attempted on rows that have no match. The 170,000 rows with no match are probably the slowest part of the query, because they need to go through the entire list of