CODEWITHSUNDEEP
mysqlsql
mrfr
mrfr

Reputation: 1794

Replace a string in every column in a specific table

I have a DB table that has many tables. I want to search each of these tables to find which has the string and then replace it with another.

What I want is if I find one field that has this string, then update it to another string

I've tried this really ugly query:

UPDATE `myTable` 

SET
c1 = 'hello',
c2 = 'hello',
c3 = 'hello',
c4 = 'hello',
c5 = 'hello',
c6 = 'hello'

WHERE 
c1 = 'hi',
c2 = 'hi',
c3 = 'hi',
c4 = 'hi',
c5 = 'hi',
c6 = 'hi'

But this is obvious wrong since if c1 is 'hello' then instead of just updating c1 to 'hi' it updates all fields to 'hi'

Upvotes: 0

Views: 41

Answers (1)

Lukasz Szozda
Lukasz Szozda

Reputation: 176064

You want conditional update:

UPDATE tbl
SET c1 = CASE WHEN c1='hi' THEN 'hello' else c1 end
   ,c2 = CASE WHEN c2='hi' THEN 'hello' else c2 end
   -- and so on...
WHERE 'hi' IN (c1,c2,c3,c4,c5);

Upvotes: 3

Related Questions