SQL Server: How to Delete or Update Rows with JOIN Clause

A common question asked in every Database Developer interview is that “How to Delete or Update Rows using JOIN Clause to filter effected rows”

Not all update and delete quires are as simple as we think. Sometime, we need to update or delete records on the basis of complex WHERE clause. And sometime we can only do this complex FILTERING through joining multiple tables.

If multiple tables are in join clause then following is the simple method to delete or update rows.

UPDATE:

UPDATE [target table]

SET [target column] = [new value]

FROM    [table1]

        INNER JOIN [table2]

ON [table1.[joining column] = [table2].[joining column]

WHERE   [condition]

USE AdventureWorks

GO

UPDATE Person.Address

SET City = 'NEWCITY'

FROM    HumanResources.EmployeeAddress eAdd

        INNER JOIN Person.Address pAdd ON eAdd.AddressID = pAdd.AddressID

WHERE   pAdd.City = 'Cambridge'

AND         eAdd.EmployeeID = 19

DELETE

DELETE  [target table]

FROM    [table1]

        INNER JOIN [table2]

ON [table1.[joining column] = [table2].[joining column]

WHERE   [condition]

USE AdventureWorks

GO

DELETE  HumanResources.EmployeeAddress

FROM    HumanResources.EmployeeAddress eAdd

        INNER JOIN Person.Address pAdd ON eAdd.AddressID = pAdd.AddressID

WHERE   pAdd.City = 'Cambridge'