MySQL: using REPLACE with a JOIN?

Question

I need to replace multiple different words in a text field.

The search and replace values are in another table.

For example the texts-table is :

The Quick Brown Fox
The Dirty Red Bandana

with a replace-table like this :

SearchValue         ReplaceValue
  Quick               Slow
  Fox                 Wolf
  Dirty               Clean
  Bandana             Hat

The replaced records would then become :

The Slow Brown Wolf
The Clean Red Hat

Is it possible to do this with a JOIN?

Something like :

UPDATE texts_table AS tt
CROSS JOIN values_table AS vt
SET tt.Text= REPLACE(tt.Text, vt.SearchValue, vt.ReplaceValue)

I tried some different ways, but couldn't get it to replace all strings in the text field.

Answer 1

You need to specify a Join condition, for example, like this:

UPDATE texts_table AS tt
INNER JOIN values_table AS vt 
   on tt.valueId = vt.valudId /*or whatever the join condition*/ 
SET tt.Text= REPLACE(tt.Text, vt.SearchValue, vt.ReplaceValue)

As specified in the syntax of the UPDATE clause:

UPDATE [LOW_PRIORITY] [IGNORE] table_reference

The table_references clause lists the tables involved in the join. Its syntax is described JOIN Syntax .