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

Tom Lane wrote:
> 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?
>
> <QUERY SNIPPED>
>
> 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.

Like so?

UPDATE
   cart_item
SET
   qty_requested_available = least(cart_item.qty_requested,
stock.qty_available)
FROM
   stock
WHERE
   cart_item.stock_id = stock.stock_id AND
   qty_requested_available <> least(cart_item.qty_requested,
stock.qty_available);


Also note the qualifier that prevents the query from updating every
cart_item row whether it needs it or not.

-Glen