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