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's still 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!