Thank you very much for your answer, this query was written to
assign packages of recording to various users.
Now I understands better the logic of firebird with first n and I am
going to use one SP with a count to resolve the problem.

Please sorry for my bad english.

Thank you.


--- In firebird-support@yahoogroups.com, Helen Borrie <helebor@t...>
wrote:

> 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