CODEWITHSUNDEEP
c#sql-servert-sql
Makmak
Makmak

Reputation: 19

Query updates all rows in the table

I am trying to update a single item's quantity in my database, but my query is updating all of the values in my quantity column.

How can this be fixed?

Here is my query:

UPDATE Stocks 
SET Stocks.Quantity = Stocks.Quantity - PointofSale.Quantity 
FROM PointofSale 
WHERE ItemID IN (SELECT ItemID FROM Recipe 
                 WHERE MenuID IN (SELECT MenuID FROM Menu 
                                  WHERE ItemName IN (SELECT Product FRPOM PointofSale 
                                                     WHERE POSID > 0)))

And below are some screenshots of my tables and DB layout.

This is my Stocks table:

This is the ID's that will call in my update query:

This is my Menu which contains all the products:

And this is my POS which has the column of quantity:

Upvotes: 0

Views: 137

Answers (2)

IVNSTN
IVNSTN

Reputation: 9299

(too long for comment)

piece of code from @Alex answer with my note from comment described:

UPDATE s SET 
  Quantity = Quantity - POS.Quantity
FROM Stocks s
INNER JOIN PointofSale AS POS
 ON ???
INNER JOIN Menu AS M
  ON M.ItemName = POS.Product
INNER JOIN Recipe AS R
  ON R.MenuID = M.MenuID
WHERE POS.POSID > 0

How is Stocks supposed to be joined to any of these tables? That is the cause of your troubles. Every row from Stocks "matches" all the rows from other tables because there is no match condition actually. This situation is called MISSING JOIN PREDICATE.

Upvotes: 2

AlexL
AlexL

Reputation: 355

Try this request:

UPDATE Stocks SET Stocks.Quantity = Stocks.Quantity - POS.Quantity
FROM PointofSale AS POS
INNER JOIN Menu AS M
  ON M.ItemName = POS.Product
INNER JOIN Recipe AS R
  ON R.MenuID = M.MenuID
WHERE POS.POSID > 0 AND Stocks.ItemID = R.ItemID

Upvotes: 1

Related Questions