Hi all, I finally realized today that I do not know how to properly remove records based on another table.
Suppose I have two tables, one with data (called BIG), and one table (SMALL) with only some id's that has to be removed from the BIG-table. I cannot get this to work, without having Firebird looping over all records in BIG naturally, once for each record in SMALL. Example: (Firebrid 3.0.4, but also holds on 2.5) -------------------------- SQL> connect "test.fdb"; Database: "test.fdb", User: JOOST SQL> create table big(id integer primary key, data varchar(10)); SQL> create table small(id integer primary key); SQL> insert into big values (1,'adfdvf'); SQL> insert into big values (2,'adfdvf'); SQL> insert into big values (3,'adfdvf'); SQL> insert into big values (4,'adfdvf'); SQL> insert into big values (5,'adfdvf'); SQL> insert into big values (6,'adfdvf'); SQL> insert into big values (7,'adfdvf'); SQL> insert into big values (8,'adfdvf'); SQL> insert into big values (9,'adfdvf'); SQL> insert into big values (10,'adfdvf'); SQL> insert into small values (5); SQL> set planonly on; SQL> delete from big where exists (select 1 from small where small.id=big.id); PLAN (SMALL INDEX (RDB$PRIMARY2)) PLAN (BIG NATURAL) -------------------------- Naturally, when BIG is really big, this takes very, very long. I've tried everything. 'where in' does not help, and I can not force it to use some other plan either. One possibility is to use an execute-block. This one is somewhat better (not really - in case small is not that small): -------------------------- SQL> set term |; SQL> execute block as declare variable v integer; CON> begin CON> for select id from small into :v do delete from big where id=:v; CON> end CON> | PLAN (BIG INDEX (RDB$PRIMARY1)) PLAN (SMALL NATURAL) -------------------------- Finally, the only good solution I could find was this: -------------------------- SQL> merge into big using small on (big.id=small.id) when matched then update set data='remove'; PLAN JOIN (SMALL NATURAL, BIG INDEX (RDB$PRIMARY1)) SQL> delete from big where data='remove'; -------------------------- Is this really the only efficient way to remove data from one table, based on another one? So set some temporary flag and then remove based on that flag? There must be another way. Regards, Joost
