Well done Set Greetings.
Walter. On Tue, Dec 16, 2014 at 4:30 PM, Svein Erling Tysvær svein.erling.tysv...@kreftregisteret.no [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 > >
