title: SQL - Updating rows based on reference data in lookup table
body: <p>I have a table, tbl_english, containing the column "word".
  I also have a table, tbl_translation, containing the columns "german_word" and "english_word".</p>
  
  <p>tbl_translation is supposed to be a lookup table to see if there are any german words in tbl_english' column "word"</p>
  
  <p>So what I want to do is;</p>
  
  <blockquote>
    <p>For each tbl_english.word,  iterate through
    tbl_translation.german_word and look for matching value.      If match
    exists, update tbl_english.word with the value in
    tbl_translation.english_word from  current row in tbl_translation</p>
  </blockquote>
  
  <p>The intention is to replace any rogue german words existing in tbl_english with their correct translation from the lookup table tbl_translation</p>
  
  <p>So far what I have come up with is this;</p>
  
  <pre><code>UPDATE tbl_english SET word = 
      (SELECT english_word FROM tbl_translation 
          WHERE tbl_english.word = german_word) 
      WHERE word IN
          (SELECT german_word FROM tbl_translation
              WHERE tbl_english.word = german_word )
  </code></pre>
  
  <p>However, this fails when there are multiple instances of the same or different words resulting from the first sub-select. Is there a simple way to solve the problem?</p>
  
  <p>Example:</p>
  
  <p>tbl_english contains;
  Mädchen
  Frau
  Boy
  Giraffe
  Baum</p>
  
  <p>tbl_translation contains (german, english);
  Mädchen, Female
  Frau, Female</p>
  
  <p>So in tbl_english I would like to see the following result;
  Female
  Female
  Boy
  Giraffe
  Baum</p>
  
  <p>Edit: Not every word in tbl_english will have a reference row in the translation table. 
  Edit2: Added example</p>