Hi, I need to update UnitinStock and Shipped Date of products based on these conditions.
1)When the shipped date is null and
2) when Quantity < Unit in stock.
But my query is updating Shipped date of all the orders even when this condition Quantity < Unit in stock is not satisfied .
How do I satisfy these 2 conditions and update colums:
Update products,orderdetails,orders
set products.UnitsInStock = (products.UnitsInStock - orderdetails.Quantity), ShippedDate = current_date()
where products.ProductID =orderdetails .ProductID and orders.OrderID = orderdetails.OrderID
and (ShippedDate is null and orderdetails.Quantity < UnitsInStock) and orders.OrderID = 11039