>> 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 >> > well, that's not the answer I wanted to get, but I've to deal with > that fact. > > I've to check wether EXECUTE STATEMENT can execute EXECUTE > BLOCKs, as the DELETE Statement is build dynamically in a proc.
Interesting, Björn, I'd expect execute block to work wherever a query could be used, but hadn't tried it inside EXECUTE STATEMENT until you said you would have to try. However, it made me curious, so I wrote: execute block returns(myanswer varchar(32)) as declare variable es varchar(500); begin es = 'execute block returns(ma varchar(32)) as ' || ' declare variable es2 varchar(500); ' || ' begin ' || ' es2 = ''execute block returns(ma2 varchar(32)) as ' || ' begin ' || ' select ''''Hooray'''' from rdb$database into ma2; ' || ' suspend; '|| ' end''; ' || ' execute statement es2 into :ma; ' || ' suspend; ' || ' end'; execute statement es into :myanswer; suspend; end just to see if it worked. It actually returned Hooray, so yes, EXECUTE STATEMENT can execute EXECUTE BLOCK and they can even be nested within each other! Set