CODEWITHSUNDEEP
phpmysql
Oleeze
Oleeze

Reputation: 35

SQL Update Query set Quantity

I am trying to create a query that will update my products Quantity column. When a customer checks out they create a row in my orders table, from there a row is created in orderdetails for every item purchased.

I have three tables with the following columns

  1. orders (ID, Quantity)
  2. orderdetails (ID, OrderID, ProductID, Quantity) "OrderID and ProductID are foreign keys"
  3. products (ID)

I would prefer the query look something like this, this is what I have so far.

UPDATE products SET Quantity = Quantity - orderdetails.Quantity INNER JOIN orderdetails ON products.ID = orderdetails.ProductID WHERE orderdetails.OrderID = orders.ID

OR

UPDATE products SET Quantity = Quantity - orderdetails.Quantity WHERE ID IN (SELECT ProductID FROM orderdetails WHERE OrderID IN (SELECT ID FROM orders WHERE ID = 104))

MySQL error is #1054 - Unknown column 'orderdetails.Quantity' in 'field list'

I am still a beginner and would appreciate a nudge in the right direction, thank you. If any additional information is required please let me know.

Upvotes: 0

Views: 2620

Answers (1)

Başar Kaya
Başar Kaya

Reputation: 364

declare @orderID int =104 -- you can set anywhere

update products set 
Quantity = Quantity - ordtl.Quantity from products p
inner join orderdetails ordtl on ordtl.ProductID = p.ID
inner join orders o on o.ID = ordtl.OrderID 
where o.ID = @orderID

-- if you know order id, you can do your want

Upvotes: 2

Related Questions