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

To select records in table b which have no corresponding key in table a:

select b.*
from table_b b
where not exists (
  select 'x'
  from table_a a
  where a.id = b.id)

To delete records in b which have no corresponding key in a:

delete from table_b b
where not exists
  (select 'x'
  from table_a a
  where a.key = b.key)

Suggestion: Put a referential integrity constraint on table_b to prevent future occurrences of this problem.

hth

Chris

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
Received on Fri Mar 12 1999 - 07:26:03 CST