Re: SQL for DELETE using subquery?
On Thu, 25 Jun 1998 15:21:02 -0700, Roger Tomas <tomasr@agcs.com>
wrote:
>Informixites...
>
>I'm trying to delete rows from one table that are not referenced
>by any rows in a second table. The referencing key consists of
>2 columns. It seems you can't use table aliases in delete
>statements to do the type of subquery I expected to use. I've
>found a way to do this (below) but it seems like a hack. Any
>better way? Schema changes are not an option. Engine is 7.23.
select a.col1 c1, a.col2 c2, b.col1 bc1
from tablea a, outer tableb b
where a.col1=b.col1
and a.col2=b.col2
into temp tmp_tbl with no log;
delete from tableb
where 1 in
(select 1 from tmp_tbl where col1=c1 and col2=c2 and bc1 is null);
OR:
select a.col1 c1, a.col2 c2, b.col1 bc1
from tablea a, outer tableb b
where a.col1=b.col1
and a.col2=b.col2
into temp tmp_tbl with no log;
delete from tmp_tbl where bc1 is not null;
delete from tableb
where 1 in
(select 1 from tmp_tbl where col1=c1 and col2=c2);
OR:
delete from tableb
where not exists
(select 1 from tablea a
where a.col1=tableb.col1
and a.col2=tableb.col2)
YMMV,
Douglas Wilson