CODEWITHSUNDEEP
sql-server-2005t-sqlssms
StackTrace
StackTrace

Reputation: 9416

Update rows in a related table based on condition in parent table

I have a table TABLE1 (PARENT TABLE) with columns

StaffID (PK)
Name
CategoryID (FK)

I also have another related table TABLE2 (RELATED TABLE) with columns

LeaveID (PK)
StaffId (FK)
StartDate

What i want to do is write a T-SQL query to update StartDate column of all rows in TABLE2 whose CategoryID in TABLE1 = '3'

TABLE2 is related to TABLE1 through the StaffID foreign key column

Upvotes: 0

Views: 488

Answers (2)

Andrew Carmichael
Andrew Carmichael

Reputation: 3113

You can use a join in the update statement, but for simple updates like this, I think the clearest way is to use a subquery. It avoids any ambiguity about which table is being updated and the optimizer will probably choose the same query plan.

update TABLE2
set StartDate = @SomeDate
where StaffId in (select StaffId from TABLE1
                  where CategoryID = 3)

Upvotes: 2

yonan2236
yonan2236

Reputation: 13659

    UPDATE TABLE2 T2 
       SET T2.StartDate = '10/10/2010' // YOUR NEW DATE
INNER JOIN TABLE1 T1 ON T1.StaffID  = T2.StaffID 
     WHERE T1.CategoryID = 3

Upvotes: 2

Related Questions