Обсуждение: trying to write a bit of logic as one query, can't seem to do it under 2

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
;

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

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

it would be that, but with greatest

thank you.  that's the exact query i was failing to write !

On Apr 21, 2010, at 8:51 PM, Glen Parker wrote:

> 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);

Jonathan Vanasco wrote:
>
> it would be that, but with greatest


Not if qty_requested_available needs to be <= qty_available...

-Glen

On Apr 21, 2010, at 9:38 PM, Glen Parker wrote:
> Not if qty_requested_available needs to be <= qty_available...


indeed, i'm an idiot this week.

thanks a ton.  this really helped me out!