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

Jonathan Vanasco <postgres@2xlp.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