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

Thanks
tkusistoAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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.  
0
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
0
tkusistoAuthor Commented:
developedtester,

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.
0
Become a Certified Penetration Testing Engineer

This CPTE Certified Penetration Testing Engineer course covers everything you need to know about becoming a Certified Penetration Testing Engineer. Career Path: Professional roles include Ethical Hackers, Security Consultants, System Administrators, and Chief Security Officers.

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:

A=English
B=English/Spanish mapping
C=Spanish

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.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
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.
0
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.

0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Query Syntax

From novice to tech pro — start learning today.