At 07:53 AM 26/01/2005 +0000, you wrote:

>Hello, I've a problem with this query :
>
>UPDATE TB_TEST SET COL='123'
>WHERE ID_TEST IN (
>SELECT FIRST 10 ID_TEST
>FROM TB_TEST)
>
>ok, that work but all the records are updated and I want to update
>only the first 10 records, there are a other solution or it's a bug
>from firebird ?

It's not a bug in Firebird, it's a case that the logic of SELECT FIRST n
can not be used in a subquery.

Because FIRST n by nature operates on an ORDERED set, it effectively
operates on the entire set that would be returned if the 'FIRST n'
qualification was not there. It literally forms the whole set and just
discards the excluded rows *after* the set has been ordered. Thus, it
should never be used for anything except output.

>With stored procedure no problem but I want use SQL Query.

The idea of "the first 10 rows in a table" has absolutely no
meaning. Ordinality is not supposed to be a physical attribute of stored
data in a relational database. Searched operations should be targeted
directly at sets whose existence is predicated on the data stored in them.

If you have a requirement to do some operation according to a FIFO rule,
then the design of your data must implement the rule to support that
requirement. If you have to fall back on some supposed "first n" to define
a set for a searched operation, and your data design can't allow you to
define that set, then your data design is inadequate. Consider, for
example, what happens if two users were to run *your* theoretical query one
after the other? Or more users? Or multiple users run this query in WAIT
transactions? There is absolutely no control over which rows get updated,
deleted, etc.

If the order of your sets is important, then you must design the table so
that it is possible to identify this order, e.g. store a date or timestamp
if chronological order is important; or store a serial number of some sort.

./heLen