Tom Lane wrote:
Jonathan Vanasco <postg...@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
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
