Обсуждение: 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
Re: trying to write a bit of logic as one query, can't seem to do it under 2
От
Jonathan Vanasco
Дата:
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
Re: trying to write a bit of logic as one query, can't seem to do it under 2
От
Jonathan Vanasco
Дата:
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!