• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 241
  • Last Modified:

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

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

  • 4
  • 2
1 Solution
Bryan ButlerCommented:
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.  
Bryan ButlerCommented:
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
tkusistoAuthor Commented:

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.
Introducing Cloud Class® training courses

Tech changes fast. You can learn faster. That’s why we’re bringing professional training courses to Experts Exchange. With a subscription, you can access all the Cloud Class® courses to expand your education, prep for certifications, and get top-notch instructions.

Bryan ButlerCommented:
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.
tkusistoAuthor Commented:
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.
Bryan ButlerCommented:
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.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

  • 4
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now