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 ;# Regards, Kjell Rilbe [Non-text portions of this message have been removed]
