Re: Need LIMIT and ORDER BY for UPDATE

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