On 8/28/19 6:47 AM, Kjell Rilbe kjell.ri...@marknadsinformation.se [firebird-support] wrote: > Den 2019-08-17 kl. 12:51, skrev Joost van der Sluis jo...@cnoc.nl > [firebird-support]: > > > > 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. > > > > This is a problem I have faced many times. The best solution I've been > able to find is an execute block statement that iterates the records in > SMALL and uses a "delete ... where big.id = :found_small_id" statement > to delete each matching BIG record. Something like this (including set > term if required for your SQL tool): > > set term #; > execute block as > declare id int; > begin > for select small.id from small into :id > do delete from big where id = :id; > end# > set term ;#
Thanks for the confirmation. I still find it strange, though. Would be nice if someone could come up with a solution for this. By improving the planning-engine (or how it is called). Or maybe adapt 'merge' so it also allows deletes. Regards, Joost.
