Recipe4.17.Deleting Records Referenced from Another Table

Recipe 4.17. Deleting Records Referenced from Another Table

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).