0

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.

David Browne - Microsoft
  • 80,331
  • 6
  • 39
  • 67
Oleeze
  • 35
  • 9
  • I noticed that you removed the sql-server tag. Should I remove my answer or did it work in mysql? – Frode Dec 13 '17 at 19:30
  • See also: https://stackoverflow.com/questions/1262786/mysql-update-query-based-on-select-query – Frode Dec 13 '17 at 19:30
  • Hello Thank you for trying to help me. I was using 104 as an example because it exists in my db, it will be replaced with a session variable later on. I've tried your queries and I've received a syntax error, currently trying to figure it out. They look like they should be working thou. Only error I spotted was ProductId should be ProdcutID. – Oleeze Dec 13 '17 at 19:35

1 Answers1

2
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

Başar Kaya
  • 354
  • 6
  • 13