StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

POSQL - Updating rows based on reference data in lookup table
text
Body
copied!<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>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload