On Tue, Dec 16, 2014 at 4:30 PM, Svein Erling Tysvær svein.erling.tysvaer@... [firebird-support] <firebird-support@yahoogroups.com> wrote:

 

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