CODEWITHSUNDEEP
mysqlreplace
user204245
user204245

Reputation: 253

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

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 query on the same column in a specific table. I want to search and replace multiple values in a table column.

Conditions 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.

Upvotes: 1

Views: 1381

Answers (2)

Mark Byers
Mark Byers

Reputation: 839124

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.

Upvotes: 0

rjmunro
rjmunro

Reputation: 28096

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.

Upvotes: 1

Related Questions