if you use firebird 2

you must use rows clause



i.e.



UPDATE INVOICE I SET I.FIELD = FOO
Rows 25





_____

From: firebird-support@yahoogroups.com
[mailto:firebird-support@yahoogroups.com] On Behalf Of Paul R. Gardner
Sent: Tuesday, December 18, 2007 5:17 PM
To: firebird-support@yahoogroups.com
Subject: [firebird-support] Question about a subquery



Can anyone explain this behavior to me? I have an INVOICE table with
100 records. I wanted to update a maximum of 25 records.
UPDATE INVOICE I SET I.FIELD = FOO
WHERE I.INVOICE_ID IN (SELECT FIRST 25 I2.INVOICE_ID FROM INVOICE I2);

To me, the subquery should have executed first returning 25 rows. It
should have had 25 non indexed or natural reads. (Running the subquery
by itself does this exactly). So this is all that should be updated.
In this case all 100 records are updated at the same time. Why is this?
It's not limited to UPDATE statements.

I realize this select statement is VERY poor, but for another example,
here's a similar statement as above rewritten as a selection:
SELECT I.INVOICE_ID FROM INVOICE I
WHERE I.INVOICE_ID IN (SELECT FIRST 25 I2.INVOICE_ID FROM INVOICE I2);
The above statement should only return 25 rows (to me). There should be
25 natural reads from the subselect, and another 25 indexed reads for
the main query since there is an index on that field. Instead I get 100
rows returned, 100 natural reads, and 100 indexed reads.

Is this a bug, or am I not understanding something?
Thanks in advance,
Paul


[Non-text portions of this message have been removed]



__________ NOD32 2730 (20071218) Information __________

This message was checked by NOD32 antivirus system.
http://www.eset.com



[Non-text portions of this message have been removed]