Re: How to find LIMIT in SQL standard

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.