Re: UPDATE/DELETE with ORDER BY and LIMIT

On 24 Sep 2010, at 21:20, Bartłomiej Korupczyński wrote:

> Hi guys,
>
> I've found some posts from 2007 about UPDATE/DELETE ... LIMIT N syntax
> and I'd like to raise it again. Current PostgreSQL of UPDATE/DELETE
> implement RETURNING statement, so extending by ORDER and LIMIT would be
> really useful.

> All that with just one query. In this specific example, the ORDER BY
> statement could be even omitted if we don't care how slots are
> distributed between users.


This probably came up in the discussion from back then as well, but what stops you from using a sub-select?

UPDATE slots
  FROM (SELECT id FROM slots WHERE user IS NULL
    ORDER BY id LIMIT 1) AS available
   SET user='joe'
 WHERE id = available.id
 RETURNING *;

Admittedly that's longer and would be slightly less efficient, but it is available now (and has been for a while) and
it'sstill in one query. 

Also:
> CREATE TABLE slots (
>     id INTEGER UNIQUE NOT NULL,
>     user VARCHAR(32),
>     expires TIMESTAMP WITH TIMEZONE,
>     -- some other columns
> );


I'd declare a primary key as what it is, not as some generic UNIQUE NOT NULL column ;) It won't make much difference in
practice,but for example, that way it's intended use is immediately clear from the table definition if people look it
up.

Alban Hertroys

--
If you can't see the forest for the trees,
cut the trees and you'll see there is no forest.


!DSPAM:737,4c9dcfe7678304776795795!