Subject Re: [firebird-support] Performance of deleting records based on another table
Author Kjell Rilbe
Den 2019-08-17 kl. 12:51, skrev Joost van der Sluis joost@...
> 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 = :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;
  for select from small into :id
  do delete from big where id = :id;
set term ;#

Kjell Rilbe

[Non-text portions of this message have been removed]