Re: delete child table rows based on parent table predicates

jim schrieb:

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

DELETE FROM address

   WHERE (last_name, first_name)

          IN (SELECT last_name, first_name
              FROM   employee
              WHERE  department_name = 'the_dept')