>Hello,
>
> Thanks for the answer.
> How can I optimize if I want to use a DML command in conjunction
> with a subselect, e.g.
>
> delete from test where Id in (
> select min(t.Id) FROM test t
> group by t.reference, t.key
> having count(*) > 1
> )
>

Hi Björn!

I don't think there is any simple way to make a delete with a subselect as the only part of a where clause perform great on largish tables. That is, using EXECUTE BLOCK (which doesn't exist on older Firebird versions) should perform OK:

execute block as
declare variable id integer;
begin
for select min(t.Id) FROM test t
group by t.reference, t.key
having count(*) > 1
into :id do
delete from test where Id = :id;
end

HTH,
Set