SQL for DELETE using subquery?

[Roger Tomas]

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.

My hack:

TableA (
col1 serial,
col2 char(m),
col3 char(n),
...
)

TableB (
col1 char(m),
col2 char(n),
...
)

select col1 || col2 as key
from TableB t
where not exists (select * from TableA
where col2 = t.col1 and col3 = t.col2)
into temp tmp with no log;

delete from TableB where col1 || col2 in
(select key from tmp);

TIA

Roger Tomas
AG Communication Systems

[Douglas Wilson]

On Thu, 25 Jun 1998 15:21:02 -0700, Roger Tomas <tom...@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

[proberts_...@informix.com]

"Yea, from the table of my memory
I'll wipe away all trivial fond records"

- Hamlet Act 1, Scene V.

I would try something like this:-

delete
from TableB
where not exists ( select 1
from TableA
where TableA.col2 = TableB.col1
and TableA.col3 = TableB.col2 )

I hope I have your 1s, 2s and 3s right.

This program posts news to thousands of machines throughout the entire
civilized world, and parts of the USA. Your message will cost the net
hundreds if not thousands of dollars to send everywhere. Please be sure
you know what you are doing.

Are you absolutely sure that you want to do this? [ny]

Bloody good value if you ask me

- Paul Roberts (not a spokesman)

In article <3592CD4E...@agcs.com>, Roger Tomas <tom...@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.
>

[Roger Tomas]

Douglas Wilson wrote:

> On Thu, 25 Jun 1998 15:21:02 -0700, Roger Tomas <tom...@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.
>

> <snip>

> OR:
>
> delete from tableb
> where not exists
> (select 1 from tablea a
> where a.col1=tableb.col1
> and a.col2=tableb.col2)

Does this do the correlation correctly?