I think i found an ok solution: (note I've updated my own insert statements
from the OP)
drop table purg;
create temporary table purg (id integer primary key, tbl);
-- Use the fact that the Puging tables C unique ID is always after table A's
data.
insert into purg
select distinct kd.id, 'a'
from c, a
where c.id = a.id
and c.cnt = a.cnt
and c.ref = a.ref
and a.id < c.id ;
-- For ech a record to be purged. Join it to get the ref value and the table B
id's
insert into purg
select distinct b.id, 'b'
from purg p, b, a
where p.id = a.id
and a.id = b.id
and a.ref = b.ref ;
insert into purg
select c.id, 'c'
from c;
--- Now purge.
delete from a where id in (select id from purg where tbl = 'a');
delete from b where id in (select id from purg where tbl = 'b');
delete from c where id in (select id from purg where tbl = 'c');
Any ideas of a better way?
Thanks,
Ken
Ken <[EMAIL PROTECTED]> wrote: Id like to get your ideas on implementing a
stack using sql tables.
table a, contains references to b
table b contains refernce to a
table c contains delete entries for A (but b must also be purged!)
My processing forces me to load all of a,b and c.
There may be cases where table C indicates a complete deletion for table A. As
a special case there is another table D that indicates a complete delete. In
this instance I can delete by another unique Id that is contained in all of the
tables but ommitted for brevity.
create table a ( id integer, ref integer, cnt integer );
create table b ( id integer, ref integer, val text );
create table c ( id integer, ref, cnt integer );
insert into a values (1,32,5);
insert into b values (11,32,'first data item');
insert into c values (2,32,5) ;
insert into a values (2,33,5);
insert into b values (12,33,'second data item');
insert into c values (3,5) ;
insert into a values (4,34,5);
insert into b values (13,34,'third data item');
After processing, Id like to be left with the following:
a ( 4, 34,5)
b (13, 34, 'third data item')
This is easily implemented in a memory stack. but I'm not sure how to
implement using sql.
thanks for any ideas.
Ken
