Hi

From Firebird_v1.5.ReleaseNotes.pdf




Two Gotchas with SELECT FIRST
1. This
delete from TAB1 where PK1 in (select first 10 PK1 from TAB1);
will delete all of the rows in the table. Ouch! the sub-select is
evaluating each 10 candidate rows for
deletion, deleting them, slipping forward 10 more...ad infinitum, until
there are no rows left.
Beware!
2. Queries like:
...
WHERE F1 IN ( SELECT FIRST 5 F2 FROM TABLE2 ORDER BY 1 DESC )
won't work as expected, because the optimization performed by the engine
transforms correlated
WHERE...IN (SELECT...) predicates to a correlated EXISTS predicate. It's
obvious that in this case FIRST
N doesn't make any sense:
WHERE EXISTS (
SELECT FIRST 5 TABLE2.F2 FROM TABLE2
Firebird 1.5 Release Notes v.1.08 5 February 2004 Page 27
WHERE TABLE2.F2 = TABLE1.F1 ORDER BY 1 DESC )

Alan McDonald wrote:

>>Sorry, I also write query with ORDER BY and it's the same result,
>>all the recordings are updated and not only 10 first ones
>>
>>UPDATE TB_TEST SET COL='123'
>>WHERE ID_TEST IN (
>>SELECT FIRST 10 ID_TEST
>>FROM TB_TEST
>>ORDER BY ID_TEST ASC)
>>
>>The subquery return the first 10 records ...
>>
>>I've write this query with EMS and IBExpert and same result.
>
>
> I see no reason for it not to work.
> You say it works in the SP.. let's see the SP code.
> I can only think that it's a data issue.
> Alan
>
>
>
>
> Yahoo! Groups Links
>
>
>
>
>
>
>