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'
Its not working...
ReplyDeleteI 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
Your query is correct. Can you please share output error
Delete