Re: delete based on multipart key
Roderick Schertler wrote:
> What's the right way to delete rows from a table which match a
> multi-part key? I've got a table with a two part key and a unique
> composite index on the key columns. I can't figure out an efficient way
> to delete all the rows whose keys appear in a second table. That is, if
> the main table contains these rows
>
> column a column b
> -------- --------
> 1 a
> 1 b
> 2 a
> 2 b
>
> and the second table contains
>
> 1 a
> 2 b
>
> I want to be left with
>
> 1 b
> 2 a
...
> I'm using SE 4.10.UE2 with isql 4.10.UD2.
...
> Roderick Schertler
> roderick@argon.org
With the engine you have (4.10), you have rowids available, so you could:
select a.rowid rowids_2_del
from tab1 a, tab2 b
where a.col_a = b.col_a
and a.col_b = b.col_b
into temp tmp_delete with no log;
delete from tab1
where rowid in (
select rowids_2_del
from tmp_delete
);
--
_____________________________________________________________________
| Colin McGrath cmm@trac3000.ueci.com |
| Raytheon Engineers & Constructors, Inc. (215) 422-4144 |
| Phila, PA, USA Standard Disclaimers Apply |
|___________________________________________________________________|