trying to write a bit of logic as one query, can't seem to do it under 2

[Jonathan Vanasco <postgres@xxxxxxxx>]

I'm trying to write a bit of logic as 1 query, but I can't seem to do  
it under 2 queries.

i'm hoping someone can help

the basic premise is that i have an inventory management system , and  
am trying to update the quantity available in the "shopping  
cart" (which is different than the independently tracked quantity  
requested ).

the logic is fairly simple:
	cart items should show the quantity_requested as available if that  
number is <= the number of items in stock, otherwise they should show  
the max number of items available

the solution i ended up with, is to just update the cart_items with  
the entire quantity_available per product, and then fix that in a  
second pass.

i'm wondering if this can be *efficiently* done within a single update  
statement.   i couldn't figure out how to do this in a single update,  
and not make multiple queries to find the actual qty_available



UPDATE
	cart_item
SET
	qty_requested_available = ( SELECT qty_available FROM stock where  
stock.id = stock_id)
;

UPDATE
	cart_item
SET
	qty_requested_available =
		CASE
			WHEN
				qty_requested_available > qty_requested THEN qty_requested
			ELSE
				qty_requested_available
		END
;


--
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general