CODEWITHSUNDEEP
sql-server-2005
MTAdmin
MTAdmin

Reputation: 1033

SQL update by inner join

Say I have two tables and would like to update the records in t1 only for those records that exist in t2.

ID = int, all others varchar

t1
id<PK>,sample,variable,status,notes

t2
sample,variable

I can select the subset from t1 by joining on t2

Select Sample FROM t1 INNER JOIN t2 ON t1.Sample = t2.Sample AND t1.Variable = t2.Variable

But what is the syntax to convert this to an update statement that does not pull update values from t2? (My values do not derive from t2; t2 merely limits which records should be updated.) 

UPDATE t1
SET Status=N'Complete',Notes=N'Lots of notes here'
INNER JOIN t2 ON t1.Sample = t2.Sample AND t1.Variable = t2.Variable

Upvotes: 1

Views: 64

Answers (1)

Bill Gregg
Bill Gregg

Reputation: 7147

You've almost got it:

UPDATE t1Aliased
SET Status=N'Complete',Notes=N'Lots of notes here'
from t1 t1Aliased 
INNER JOIN t2 ON t1Aliased.Sample = t2.Sample 
AND t1Aliased.Variable = t2.Variable

Upvotes: 3

Related Questions