SQL for DELETE using subquery?
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