SQL update by inner join

Question

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

Bill Gregg · Accepted Answer · 2013-06-21 21:02:06Z

3

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

answered Jun 21, 2013 at 21:02

Bill Gregg

7,1472 gold badges24 silver badges40 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

MTAdmin Over a year ago

Hmmm, thought I'd tried that. Maybe it was the alias on the From table...Anyway, thanks!

Bill Gregg Over a year ago

If you could "Accept" so that this question is marked as answered, I'd appreciate it.

MTAdmin Over a year ago

Sure, looks like there's a 5-10 minute minimum before Acceptance is allowed.

Collectives™ on Stack Overflow

SQL update by inner join

1 Answer 1

3 Comments

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Related