Re: Deleting from a table using join / set operators.

Dave Bevan wrote:
>
> I wonder if anyone can help me with the following SQL problem, as I am
> pretty rusty with it at the moment.
>
> I have two tables
>
> Table A
> ID char 4
> RANK char 1
>
> Table B
> ID char 4
> REASON char 3
>
> There is a one to many relationship between Table A and Table B,
> however there are records on table B which do not have an ID and table
> A, and I want to delete them.
>
> If I do
> SELECT ID from B MINUS SELECT ID from A
>
> I get a list if those IDs which do not match, however I want to do the
> following ...
>
> 1. I want to display the ID and REASON for all the records in table B
> which do not have an ID in table A. How do I do this ? I presume its
> to do with putting a NULL or somesuch dummy field for A in the select,
> but can't work it out.
>
> 2. I want to then deleted them, but
> DELETE FROM B where (SELECT ID from B MINUS SELECT ID from A)
> does not work. Why is this, and what command should I issue?
>
> Any help would be most appreciated ?
>
> Thanks.
>
> --
> Dave Bevan
> School Improvement Unit
> Education Department
> Cornwall County Council

Hi:

1. For this you need to do something like this

    select id, reason from B
    where id not in (select id from A);

 2. delete from B where ID in

    (select ID from B

     minus
     select ID from A);

Hope that helps

Hari Received on Fri Mar 12 1999 - 09:58:44 CST