CODEWITHSUNDEEP
mysqlreplace
Mazatec
Mazatec

Reputation: 11649

MySQL - How to find and replace over multiple tables

I have inherited a mess of a database where I have 4 tables, each with a field called keywords. The keywords field contains strings like this:

Row 1 -> 'blue,car,sunny,green,day'
Row 2 -> 'some,cool,keywords,green,nice'
Row 3 -> 'red,truck,fast,happy'

And so on...

I am looking to be able to do a find and replace across multiple tables at the same time. Something like, find all instances of the string 'green' and replace it with the string ''

I've tried:

UPDATE table_name1, table_name2, table_name3, table_name4 SET keywords = replace(keywords, 'green', '');

but the error says "Column 'keywords' in field list is ambiguous", I understand why this is saying this (as the keywords column appears in both tables..)

How do I reference all 4 tables in a single statement?

I am keen not to put the update statement in a loop...

Upvotes: 0

Views: 1206

Answers (1)

Bob Lied
Bob Lied

Reputation: 621

The tables must be joined. http://dev.mysql.com/doc/refman/5.5/en/update.html Then reference the keywords as table_name1.keywords, table_name2.keywords, etc.

Upvotes: 1

Related Questions