Re: SQL for DELETE using subquery?
"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.EE99BFE2@agcs.com>, 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.
>
>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