>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
Advertisements

Posted on February 15, 2011, in Sql Server 2005, Sql Server 2008, SQL Server Basics, TSQL Tips n Tricks. Bookmark the permalink. 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_SALARYI 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 35000EMPLOYEE table is:EMP_ID EMP_NAME 1 VIJAY 2 SANTOSH 3 RAKESH

  2. Your query is correct. Can you please share output error

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: