0

Could really use some help with an update query...(SQL Serer 2008 R2 Express) I have two tables, tblJP and tblMaster.

I only have a string field that matches between the two tables.

tblJP AND tblMaster

I need to update tblJP.LangString with tblMaster.Long_text when

tblJP.short_text = tblMaster.short_text AND tblMaster.Lang = 'jp'

Any help would be greatly appreciated. I am spinning my wheels trying all sorts of logic and syntax from creating temp tables to other types of joins all with no luck.

htm11h
  • 1,739
  • 8
  • 47
  • 104

2 Answers2

6

A simple update with an INNER JOIN should do the trick.

UPDATE     tblJP
SET        tblJP.LangString = tblMaster.Long_Text
FROM       tblJP
INNER JOIN tblMaster ON tblMaster.alt_text = tblJP.short_text
WHERE      tblMaster.Lang = 'jp'

WARNING: Never run an update statement against your production server without first testing it against a development server - especially when someone else wrote the SQL.

James Hill
  • 60,353
  • 20
  • 145
  • 161
  • +1 For the disclaimer! This can cause indeterminate results if there is more than one matching row from the join (an issue avoided with `merge`) – Martin Smith Oct 21 '11 at 15:52
  • 1
    Also, regarding James' warning: I like to start any update statement with a `BEGIN TRANSACTION` and close with `-- COMMIT`. Then I can check the number of rows affected and select and F5 to commit if it is what I expect. (Although this doesn't verify the data change...) – Jamie F Oct 21 '11 at 15:53
1

You could also use MERGE

MERGE INTO tblJP
USING (SELECT *
       FROM   tblMaster
       WHERE  Lang = 'jp') AS SOURCE
ON SOURCE.alt_text = tblJP.short_text
WHEN MATCHED THEN
  UPDATE SET LangString = SOURCE.Long_Text;

In the event that the JOIN returns multiple rows you will be alerted to the problem with an error The MERGE statement attempted to UPDATE or DELETE the same row more than once.

Martin Smith
  • 438,706
  • 87
  • 741
  • 845
  • Thanks for posting this. I saw your comment, but I've never used `MERGE` before so I wasn't sure how to incorporate that into my answer. – James Hill Oct 21 '11 at 16:20