Tuesday, February 15, 2011

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'

2 comments:

  1. Its not working...

    I am using this query
    "DELETE rdg.EMPLOYEE FROM rdg.EMPLOYEE e JOIN rdg.EMP_SALARY s ON s.EMP_ID = e.EMP_ID;"

    I have two tables EMPLOYEE & EMP_SALARY
    I want to delete the rows from EMPLOYEE table whose id is present in EMP_SALARY table.


    EMP_SALARY table is:
    EMP_ID SALARY
    1 40000
    2 30000
    4 35000

    EMPLOYEE table is:
    EMP_ID EMP_NAME
    1 VIJAY
    2 SANTOSH
    3 RAKESH

    ReplyDelete
    Replies
    1. Your query is correct. Can you please share output error

      Delete

All suggestions are welcome