Deleting Records Referenced from Another Table - SQL Server

Problem

You want to delete records from one table when those records are referenced from some other table. Consider the following table, named DEPT_ACCIDENTS, which contains one row for each accident that occurs in a manufacturing business. Each row records the department in which an accident occurred and also the type of accident.

 
 create table dept_accidents
 ( deptno         integer,
   accident_name  varchar(20) )

 insert into dept_accidents values (10,'BROKEN FOOT')
 insert into dept_accidents values (10,'FLESH WOUND')
 insert into dept_accidents values (20,'FIRE')
 insert into dept_accidents values (20,'FIRE')
 insert into dept_accidents values (20,'FLOOD')
 insert into dept_accidents values (30,'BRUISED GLUTE')

 select * from dept_accidents

     DEPTNO ACCIDENT_NAME
 ---------- --------------------
         10 BROKEN FOOT
         10 FLESH WOUND
         20 FIRE
         20 FIRE
         20 FLOOD
         30 BRUISED GLUTE

You want to delete from EMP the records for those employees working at a department that has three or more accidents.

Solution

Use a subquery and the aggregate function COUNT to find the departments with three or more accidents. Then delete all employees working in those departments:

 1 delete from emp
 2  where deptno in ( select deptno
 3                      from dept_accidents
 4                     group by deptno
 5                    having count(*) >= 3 )

Discussion

The subquery will identify which departments have three or more accidents:

 
 select deptno
   from dept_accidents
  group by deptno
 having count(*) >= 3

     DEPTNO
 ----------
         20

The DELETE will then delete any employees in the departments returned by the subquery (in this case, only in department 20).

** If you want the Full Table detail. Refer the SQL Table in Label List. Or Click here to View the Table