On 8/28/19 6:47 AM, Kjell Rilbe kjell.rilbe@...
[firebird-support] wrote:

> 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 ;#

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.