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

On Thu, Sep 08, 2005 at 10:49:25PM -0400, Tom Lane wrote:
> Alvaro Herrera <alvherre@alvh.no-ip.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@pervasive.com
Pervasive Software      http://pervasive.com    work: 512-231-6117
vcard: http://jim.nasby.net/pervasive.vcf       cell: 512-569-9461