delete child table rows based on parent table predicates

Giganews Newsgroups
Subject: delete child table rows based on parent table predicates
Posted by:  jim (jx…@admin.fsu.edu)
Date: Fri, 9 Jan 2004

I have two tables that are related by keys. For instance,

Table employee {
    last_name char(40) not null,
    first_name char(40) not null,
    department_name char(40) not null,
    age int not null,
    ...
}
Employee table has a primary key (combination of last_name and first_name).

Table address {
    last_name char(40) not null,
    first_name char(40) not null,
    street char(200) not null,
    city char(100) not null,
    ...
}
Address table has a primary key (combination of last_name, first_name and
street in which (last_name, first_name) reference (last_name, first_name) in
employee table.

Now I want to delete some rows in Address table based on department_name in
Employee table. What is sql for this delete?

I appreciate your help. Please ignore table design and I just use it for my
problem illustration.

Jim

Replies