From: | "John D(dot) Burger" <john(at)mitre(dot)org> |
---|---|
To: | General PostgreSQL List <pgsql-general(at)postgresql(dot)org> |
Subject: | Re: Need LIMIT and ORDER BY for UPDATE |
Date: | 2007-12-13 04:48:06 |
Message-ID: | 87FD4634-549D-45D8-9063-5B8506B00A54@mitre.org |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
D. Dante Lorenso wrote:
> I'd really like to have ORDER BY and LIMIT for UPDATE and DELETE
> commands. Is this possible?
>
> UPDATE invoice i
> SET reserve_ts = NOW() + '1 hour'::timestamp
> FROM account a
> WHERE a.acct_id = i.acct_id
> AND i.reserve_ts < NOW()
> AND a.status = 'A'
> AND i.is_paid IS FALSE
> ORDER BY i.create_ts ASC
> LIMIT 1
> RETURNING invoice_id;
>
> This query would find JUST ONE invoice record which is not paid and
> reserve the right to operate on the row using the 'reserve_ts'
> column for all active accounts. The one row would be the oldest
> invoice matching the criteria. Only that one row would be updated
> and the invoice_id of the updated row (if any) would be returned.
> Can something like what I want be added in a future version? Ideas
> or alternatives? I don't see how I can rewrite this query as a
> single statement any other way and get the same expectations.
Doesn't this do it, assuming invoice_id is unique?
UPDATE invoice
SET reserve_ts = NOW() + '1 hour'::timestamp
where invoice_id =
(select invoice_id from invoice i,
account a
WHERE a.acct_id = i.acct_id
AND i.reserve_ts < NOW()
AND a.status = 'A'
AND i.is_paid IS FALSE
ORDER BY i.create_ts ASC
LIMIT 1)
RETURNING invoice_id;
- John Burger
MITRE
From | Date | Subject | |
---|---|---|---|
Next Message | Tatsuo Ishii | 2007-12-13 05:10:45 | timestamp with time zone |
Previous Message | Bill Moran | 2007-12-13 04:43:19 | Re: Need LIMIT and ORDER BY for UPDATE |