title: Find and replace string in MySQL using data from another table
body: <p>I have two MySQL tables, and I want to find and replace text strings in one using data in another.</p>
  
  <p>Table <code>texts</code>:</p>
  
  <pre><code>+---------------------+
  |      messages       |
  +---------------------+
  | 'thx guys'          |
  | 'i think u r great' |
  | 'thx again'         |
  | ' u rock'           |
  +---------------------+
  </code></pre>
  
  <p>Table <code>dictionary</code>:</p>
  
  <pre><code>+--------------+---------------+
  | bad_spelling | good_spelling |
  +--------------+---------------+
  |    'thx'     |    'thanks'   |
  |    ' u '     |    ' you '    |
  |    ' r '     |    ' are '    |
  +--------------+---------------+
  </code></pre>
  
  <p>I want SQL to go through and look at every row in messages and replace every instance of bad_spelling with good_spelling, and to do this for all the pairs of bad_spelling and good_spelling.</p>
  
  <p>The closest I have gotten is this:</p>
  
  <pre><code>update texts, dictionary
  set texts.message = replace(texts.message,
                              dictionary.bad_spelling,
                              dictionary.good_spelling)
  </code></pre>
  
  <p>But this only changes "thx" to "thanks" (in two rows) and does not go on to replace " u " with " you" or " r " with " are ."</p>
  
  <p>Any ideas how to make it use all the rows in dictionary in the replace statement?</p>
  
  <hr>
  
  <p>PS forgot to mention that this is a small example and in the real thing I will have a lot of find/replace pairs, which may get added to over time.</p>