What is the most efficient way to make SQL update all text fields in a table with a language substitution from another table.

Posted on 2008-11-03
Last Modified: 2012-05-05
With Mydata
T1               T2             T3          T4      T5
Missing       Broken                  
               Missing      Broken            
Bad               b              c          d              e
a               b              c          d              e

and Language
English      Whateverelse
Missing      Gnissim
Broken      Nekorb
a              z
b              f

Rather than doing 1 of these for every text field in the table.
UPDATE MyData INNER JOIN [Language] ON MyData.T1 = Language.English SET MyData.T1 = [language].[whateverelse]

Is there a way to say
if any text field in MyData matches any text in language.english replace it with the associated value from language.whateverelse
Field size is not an issue.

End result desired
Mydata contains
T1               T2             T3          T4      T5
Gnissim       Nekorb                  
               Gnissim      Nekorb            
Bad               f              c          d              e
z               f              c          d              e

Question by:tkusisto
    LVL 16

    Expert Comment

    by:Bryan Butler
    I think you would need a programming language to do this.  Basic SQL doesn't have the ability to do this, except the way you described.  Can you use an sql language such as TSQL?  That might be able to do it.  

    Here's the steps for something like this:

    Create list with all unique elements from MyData (X).
    Loop through X -
       Look up translations in language.english(Y) for each element
       store in an array

    Loop through Y -
      Update MyData for each Y

    Is speed an issue?  If you can update a bunch at one time, such common words (ex the, she, them), that would be much faster than doing it one at a time.  There are other things you can do if speed is an issue.  
    LVL 16

    Expert Comment

    by:Bryan Butler
    That should have been:

    Create list with all unique elements from MyData (X).
    Loop through X -
       Look up translations in language.english for each element
       store in an LIST (Y)

    Loop through Y -
      Update MyData for each element of Y

    Author Comment


    I think you may have provided the best answer and may deserve the points but I hope not. I am afraid if that is the ultimate answer I will be taking my race horse and hitching it to a plow.

    I won't wait forever but I will wait another day (gotta vote anyway) to see if someone else has a better answer. Thanks for your help.
    LVL 16

    Accepted Solution

    Plowing can be fun ;)  Wait, couldn't you create a 3rd table, even on the fly, that has the "mapping", and then inner join....hmmm....something like this:

    B=English/Spanish mapping

    B.english B.spanish

    Updating English with Spanish terms:

    update A set A.x=B.x.spanish where A.x=(select B.x.english from B where B.x.english=C.x.) and
    B.x.spanish=(select C.x from C where C.x=B.x.spanish)

    Nope, that's boils down to what you're saying.  Back to the drawing pad.

    Author Closing Comment

    The question as stated does not appear to have a better solution than the one outlined in the question. Oh well developedtester did respond and I do appreciate that.
    LVL 16

    Expert Comment

    by:Bryan Butler
    Thanks for the points.  I'm still trying.  I think there's got to be some other way.  If we look at the subqueries separately:

    select B.english from B where B.english in C
    - gives you all the things from C that have an english translation,

    select C from C where C in B.spanish
    - gives you the same thing...doh...back to the drawing board.


    Featured Post

    Looking for New Ways to Advertise?

    Engage with tech pros in our community with native advertising, as a Vendor Expert, and more.

    Join & Write a Comment

    In database programming, custom sort order seems to be necessary quite often, at least in my experience and time here at EE. Within the realm of custom sorting is the sorting of numbers and text independently (i.e., treating the numbers as number…
    Introduction Hopefully the following mnemonic and, ultimately, the acronym it represents is common place to all those reading: Please Excuse My Dear Aunt Sally (PEMDAS). Briefly, though, PEMDAS is used to signify the order of operations (http://en.…
    It is a freely distributed piece of software for such tasks as photo retouching, image composition and image authoring. It works on many operating systems, in many languages.
    Sending a Secure fax is easy with eFax Corporate ( First, Just open a new email message.  In the To field, type your recipient's fax number You can even send a secure international fax — just include t…

    734 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    23 Experts available now in Live!

    Get 1:1 Help Now