Den 2019-08-17 kl. 12:51, skrev Joost van der Sluis joost@...
[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]