Re: How to find LIMIT in SQL standard

Thanks for the reply, Stephan. Sorry about the late reply.

Your dissection of the standard (and thanks for the translation, by
the way!) is a clear reason to use unique row ids. Unfortunately the
project I am reporting bugs on does not do this at present.

Many kind regards,
Rory

On 22/09/03, Stephan Szabo (sszabo@megazone.bigpanda.com) wrote:
> On Mon, 22 Sep 2003, Rory Campbell-Lange wrote:
>
> > I have to file a bug about the implementation of some delivery report
> > database calls in the Kannel (open source WAP/SMS) server code.
> >
> > Essentially the call (as defined below) asks for an update and adds a
> > LIMIT parameter on the end of the UPDATE. (eg update where x=1 limit 1).
> > Postgres doesn't like this and I assume it isn't SQL standards
> > compliant and need to refer to this in my bug report.
>
> Well, for SQL92, it looks like the correct section to start in is
> 13.10 (update statement: searched) which looks like:
>
> UPDATE <table name>
>  SET <set clause list>
>  WHERE <search condition>
>
> The last of those is the interesting one which is 8.12 (search condition)
>
>
>          <search condition> ::=
>                 <boolean term>
>               | <search condition> OR <boolean term>
>
>          <boolean term> ::=
>                 <boolean factor>
>               | <boolean term> AND <boolean factor>
>
>          <boolean factor> ::=
>               [ NOT ] <boolean test>
>
>          <boolean test> ::=
>               <boolean primary> [ IS [ NOT ] <truth value> ]
>
>          <truth value> ::=
>                 TRUE
>               | FALSE
>               | UNKNOWN
>
>          <boolean primary> ::=
>                 <predicate>
>               | <left paren> <search condition> <right paren>
>
>
> Then 8.1 (predicate)
>
>          <predicate> ::=
>                 <comparison predicate>
>               | <between predicate>
>               | <in predicate>
>               | <like predicate>
>               | <null predicate>
>               | <quantified comparison predicate>
>               | <exists predicate>
>               | <unique predicate>
>               | <match predicate>
>               | <overlaps predicate>
>
> Also, since tables are effectively unordered, unless the other
> where conditions are guaranteed to get a single row anyway which
> row is modified is fairly indeterminate; this is only interesting
> because it means that you don't necessarily get the same row
> as a previous select (if any) would get. That's not always important,
> but since update also has no way to order the rows that I know of,
> if it were important you couldn't really get around it.