Forum Stats

  • 3,734,173 Users
  • 2,246,901 Discussions
  • 7,857,150 Comments

Discussions

Update statement is not updating the right values

Gayathri Venugopal
Gayathri Venugopal Member Posts: 108
edited Mar 30, 2019 11:29AM in MySQL Community Space

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

Tagged:
John Thorton

Answers

  • Gaz in Oz
    Gaz in Oz Member Posts: 3,776 Bronze Crown
    edited Mar 30, 2019 7:25AM

    Your update syntax suggests you are working with MYSQL db.

    It always helps to format your code into a more readable and maintainable style, for example:

    Update products,       orderdetails,       ordersset    products.UnitsInStock = (products.UnitsInStock - orderdetails.Quantity),       ShippedDate = current_date()where  products.ProductID = orderdetails.ProductIDand    orders.OrderID = orderdetails.OrderIDand   (ShippedDate is null and       orderdetails.Quantity < UnitsInStock)and    orders.OrderID  = 11039;

    The fact the update is updating rows you don't want it to means you haven't got the join to the three tables correct.

    Write it as a select statement first then convert it into an update when you are sure it will update the rows you want it to.

    ...and as this looks like a mysql question you be better off asking your question here or

    here http://forums.mysql.com/

  • John Thorton
    John Thorton Member Posts: 14,493 Silver Crown
    edited Mar 30, 2019 10:19AM
    Gayathri Venugopal wrote:Hi, I need to update UnitinStock and Shipped Date of products based on these conditions.1)When the shipped date is null and2) 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,ordersset products.UnitsInStock = (products.UnitsInStock - orderdetails.Quantity), ShippedDate = current_date()where products.ProductID =orderdetails .ProductID and orders.OrderID = orderdetails.OrderIDand (ShippedDate is null and orderdetails.Quantity < UnitsInStock) and orders.OrderID = 11039

    Please click on URL below & provide details as stated in #5 - #9 inclusive

    How do I ask a question on the forums?

    Please post full results from SQL below

    SELECT * FROM V$VERSION;

  • mathguy
    mathguy Member Posts: 9,778 Gold Crown
    edited Mar 30, 2019 10:34AM

    You were told two days ago, in a different thread you started, that this is NOT A MySQL FORUM.   Repeat:  This is NOT A MySQL FORUM.  Is that hard to understand?

    John Thorton
  • Timo Hahn
    Timo Hahn Senior Principal Technical Consultant - Oracle ACE Director Member, Moderator Posts: 37,085 Red Diamond
    edited Mar 30, 2019 11:28AM

    ***Moderator action (Timo): User, I moved you thread to the MySql space as it isn't about OracleDB.***


Sign In or Register to comment.