I want to search and replace multiple values in a table column in one MYsql querry.

Question

Query to Where X update Y and where A update B in a Mysql Table column.

How can I Do this in MYsql in one querry on the same column in a specific table. I want to search and replace multiple values in a table column.

Conditons in table1 in column1

Where X update Y 
and
Where a update b
and
Where 1 update 2
and
Where 3 update 4
and
Where value1 update value 2

And So on.

I can individually do this but how can I do this faster? Is there a mysql function to help me with this?

I have about 120 columns with 200 search and replace/update values for each column.

Thanks.

Answer 1

You can use something like this:

update table table set A = if(conditionA, newA, A), B = if(conditionB, newB, B)

But I expect it will be slower than 2 separate updates, because it is trying to reset every row's value back to itself when it doesn't match the condition.

You could optimise it somewhat by adding:

update table table set A = if(conditionA, newA, A), B = if(conditionB, newB, B)
where conditionA or conditionB

This might be quicker than 2 queries in some circumstances.

Answer 2

You could do something like this:

UPDATE table1
SET
    col1 = CASE WHEN col2 THEN a ELSE col1 END,
    col3 = CASE WHEN col4 THEN b ELSE col3 END

Note that this sets a value back to itself if the condition fails, effectively causing it to be unchanged.