Delete from tables involved in a SQL Join

This question is not asked that often in an interview, but you may have to write a query to delete from tables that are involved in a SQL join in your day to day job routine as a SQL Developer.


Let me explain you the question first. Consider the Employees and Departments tables below.
I want you to write a query using joins which will give me the list of all HR Department employees. The output should be as shown below.




To do this we will write a query using joins as shown below.

Select E.Name as [Employee Name],D.Name as [Department Name]
from Employees E
Inner Join Departments D
On E.DeptId = D.Id
Where D.Name = 'HR'

Now I want you to write a query, that deletes all the HR department employees. To do this we have to join Employees and Departments tables and usually we will be tempted to write the query as shown below, but this query will not execute and will give a syntax error.

Incorrect SQL Delete Query to delete from multiple tables invloved in a SQL join
Delete From Employees E
Inner Join Departments D
On E.DeptId = D.Id
Where D.Name = 'HR'

The query below shows the correct syntax for issuing a SQL delete that involves a SQL join.
Delete E From (Employees E
Inner Join Departments D
On E.DeptId = D.Id
and D.Name = 'HR')

The following syntax is also valid and has the same effect.
Delete Employees From
(
Employees join Departments
on Employees.DeptId = Departments.Id
)
where Departments.Name = 'HR'

6 comments:

  1. how to delete duplicate records from table.

    ReplyDelete
    Replies
    1. Using CTC statement in Sql Server
      with tbltem as(
      Select Row_Number() over(Partition by Name order by Name) as RowNumber,* from Employee)Delete from tbltem where RowNumber>1

      Delete
  2. delete Employees
    from Employees
    t1 inner join Departments t2 on t1.DeptId = t2.Id
    where t2.Name = 'HR'

    ReplyDelete
  3. @Anonymous May 13, 2013 at 1:20 PM:

    Your query deletes all the matched records. But in the case where more than one of the same record if exists, deletion should be done leaving one record. In that case how should we perform deletion?

    ReplyDelete
  4. Some Friend asking about duplicate deletion.
    I think, Check your relation could be irrelevant or your joining could have mistake if you are working with Relation.

    ReplyDelete
  5. what if the table you tent to delete are been referenced by other objects ?

    ReplyDelete

If you are aware of any other sql server questions asked in an interview, please post them below. If you find anything missing or wrong, please feel free to correct by submitting the form below.

 
Disclaimer - Terms of use - Contact Us