Query optimization with case argument

Question

I have a column with codes. Now every code has been changed to something else. I am trying to update it. Therefore, I used the statement casein mysql. But the problem is that I have about 250,000 lines and 80,000 unique codes that need to be replaced. And the case statement takes like 10 minutes to execute. Any better approach for this.

My query looks like this:

UPDATE test_table
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)
ELSE code

Answer 1

The operator caseadds time because it is running.

Decision? Store pairs in a temporary table., With 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'),
           . . . ;

Then use updatewith join:

update test_table tt join
       code_paris cp
       on tt.code = cp.old_code
    set tt.code = cp.new_code;

, , case. , , . 170 000 , , , case.