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

Jonathan Vanasco <postgres(at)2xlp(dot)com> writes:
> I'm trying to write a bit of logic as 1 query, but I can't seem to do
> it under 2 queries.

Uh, why can't you just push that CASE expression into the sub-select?

UPDATE
cart_item
SET
qty_requested_available =
( SELECT CASE
WHEN
qty_available > qty_requested THEN qty_requested
ELSE
qty_available
END
FROM stock where stock.id = stock_id )
;

You might have to qualify qty_requested here to make sure it comes from
cart_item, if there's a column of the same name in stock.

BTW, I'd suggest using GREATEST() instead of the CASE, but that's
just a minor improvement.

regards, tom lane