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.