Re: Support for Limit in Update, Insert...

On Thu, Sep 08, 2005 at 10:49:25PM -0400, Tom Lane wrote:
> Alvaro Herrera <alvherre(at)alvh(dot)no-ip(dot)org> writes:
> > This has been discussed before, and rejected. Please see the archives.
>
> For SELECT, both LIMIT and OFFSET are only well-defined in the presence
> of an ORDER BY clause. (One could argue that we should reject them when
> no ORDER BY, but given that the database isn't getting changed as a side
> effect, that's probably too anal-retentive. When the database *is*
> going to be changed, however, I for one like well-defined results.)
>
> If this proposal included adding an ORDER BY to UPDATE/DELETE, then it
> would at least be logically consistent. I have not seen the use-case
> for it though. In any case you can usually get the equivalent result
> with something like
>
> UPDATE foo SET ...
> WHERE pkey IN (SELECT pkey FROM foo ORDER BY ... LIMIT ...);

BTW, this is a case where using ctid would make sense, though you can't:

decibel=# update rrs set parent=parent+1 where ctid in (select ctid from
rrs order by rrs_id limit 1);
ERROR: could not identify an ordering operator for type tid
HINT: Use an explicit ordering operator or modify the query.
ERROR: could not identify an ordering operator for type tid
HINT: Use an explicit ordering operator or modify the query.
decibel=#

--
Jim C. Nasby, Sr. Engineering Consultant jnasby(at)pervasive(dot)com
Pervasive Software http://pervasive.com work: 512-231-6117
vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461