Thursday, July 23, 2009

Delete Based on Another Table

I recently had to delete records in one table based on the data in another table. This isn't particularly tricky, but the only examples I could find online ended up deleting all the records in the table.

The code at the beginning is just to set up a table I can use as an example.
Conn Scott/Tiger
Set Feedback On Echo On

Select *
From Dept;

Drop Table Dept2 Purge;

Create Table Dept2 As
Select * From Dept;

Insert Into Dept2 Values(50,'HR','PITTSBURGH');
Insert Into Dept2 Values(60,'HOUSEKEEPING','LA');
Insert Into Dept2 Values(70,'MANAGEMENT','ATLANTA');

Select * From Dept2;
















This code deletes from the newly created table where a record exists in the original table that has a matching key.
Delete From Dept2
Where Exists
(Select 1
From Dept
Where Dept.Deptno = Dept2.Deptno);

Select * From Dept2;

No comments:

Post a Comment