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

Question

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

Answer 1

    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

Answer 2

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)