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@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
