title: How to get an Update Statement to change the values depending on entries from another Table
body: <p>I have an Inventory Management Database in SQL Server. I have four tables. Items, Sales, Orders and Locations.</p>
  
  <pre><code>/*Start of Table Creations*/
  CREATE TABLE Items
  (
      itemID INT,
      itemName VARCHAR(70) NOT NULL,
      quantityOnHand INT CONSTRAINT quantityOnHandCheck CHECK (quantityOnHand &gt;= 0),
      quantityOnOrder INT CONSTRAINT quantityOnOrderCheck CHECK (quantityOnOrder &gt;= 0)
  
      CONSTRAINT ItemsPK
          PRIMARY KEY (itemID)
  )
  
  CREATE TABLE Sales
  (
      invoiceID INT,
      itemID INT NOT NULL,
      quantity INT CONSTRAINT salesquantityCheck CHECK (quantity &gt;= 1),
      location INT,
      InvoiceDate DATE
  
      CONSTRAINT SalesPK
          PRIMARY KEY (invoiceID)
  )
  
  CREATE TABLE Orders
  (
      orderID INT,
      itemID INT NOT NULL,
      quantity INT CONSTRAINT ordersquantityCheck CHECK (quantity &gt;= 1),
      orderDate DATE,
      delivered VARCHAR(1) CONSTRAINT ordersdeliveredCheck CHECK (delivered = 'Y' OR delivered = 'N')
  
      CONSTRAINT OrdersPK
          PRIMARY KEY (orderID)
  )
  
  CREATE TABLE Locations
  (
      locationID INT,
      name VARCHAR(70) NOT NULL,
      address VARCHAR(70),
      street VARCHAR(70),
      city VARCHAR(70),
      province VARCHAR(2),
      country VARCHAR(25) CONSTRAINT countryDefault DEFAULT 'Canada',
      zip VARCHAR(7)
  
      CONSTRAINT LocationsPK
          PRIMARY KEY (locationID)
  )
  /*End of Table Creations*/
  </code></pre>
  
  <p>I have a few inserts which inserts sample data into these tables.</p>
  
  <pre><code>/*Insert current Inventory - Start*/
  INSERT INTO Items
      (itemID, itemName, quantityOnHand, quantityOnOrder)
  VALUES
      (1, 'Mocha Java', 3, 0),
      (2, 'Stevia', 5, 0)
  /*Insert current Inventory - End*/
  
  /*Insert current Invoices - Start*/
  INSERT INTO Sales
      (invoiceID, itemID, quantity, location, InvoiceDate)
  VALUES
      (1, 1, 2, 1, GETDATE()),
      (2, 2, 1, 2, GETDATE())
  /*Insert current Invoices - End*/
  
  /*Insert current Orders - Start*/
  INSERT INTO Orders
      (orderID, itemID, quantity, orderDate, delivered)
  VALUES
      (1, 1, 6, GETDATE(), 'N'),
      (2, 2, 2, GETDATE(), 'N'),
      (3, 1, 6, GETDATE() + 1, 'N')
  /*Insert current Orders - End*/
  
  /*Insert current Locations - Start*/
  INSERT INTO Locations
      (locationID, name, address, street, city, province, country, zip)
  VALUES
      (1, 'CVO', 2106, 'Gordon Street', 'Guelph', 'ON', 'Canada', 'N1L 1G6'),
      (2, 'St John Ambulance (Kitchener Waterloo Branch)', 250, 'Gage Ave', 'Kitchener', 'ON', 'Canada', 'N2M 2C8')
  /*Insert current Locations - End*/
  </code></pre>
  
  <p>Then I have a Bunch of Update statements</p>
  
  <pre><code>/*Update Inventory after a Sale was made - Start*/
  UPDATE Items
      SET Items.quantityOnHand = Items.quantityOnHand - Sales.quantity
      FROM Items
      INNER JOIN Sales
      ON Sales.itemID = Items.itemID
  /*Update Inventory after a Sale was made - Start*/
  
  /*Update Inventory after an Order was made - Start*/
  UPDATE Items
      SET Items.quantityOnOrder = Items.quantityOnOrder + Orders.quantity
      FROM Items
      INNER JOIN Orders
      ON Items.itemID = Orders.itemID
  /*Update Inventory after an Order was made - End*/
  
  /*Update Inventory after an Order was delivered - Start*/
  UPDATE Items
      SET Items.quantityOnHand = Items.quantityOnHand +  Orders.quantity,
      Items.quantityOnOrder = Items.quantityOnOrder - Orders.quantity
      FROM Items
      INNER JOIN Orders
      ON Orders.itemID = Items.itemID
      WHERE Orders.delivered = 'Y'
  /*Update Inventory after an Order was delivered - End*/
  </code></pre>
  
  <p>And Finally I have Select Statements to reflect these manipulations.</p>
  
  <pre><code>SELECT * FROM Items
  
  /*SELECT * FROM Items
      WHERE quantityOnHand &lt; 2*/
  
  SELECT Sales.invoiceID, Items.itemName AS 'Item Name', Sales.quantity, Locations.name AS 'Location', DATENAME(mm, Sales.InvoiceDate) + ' ' + DATENAME(dd, Sales.InvoiceDate) + ', ' + DATENAME(yyyy, Sales.InvoiceDate) AS 'Invoice Date'
      FROM Sales
      INNER JOIN Items
      ON Sales.itemID = Items.itemID
      INNER JOIN Locations
      ON Sales.location = Locations.locationID
      ORDER BY Items.itemID ASC
  
  SELECT Orders.OrderID, Items.itemName AS 'Item Name', Orders.quantity, DATENAME(mm, Orders.OrderDate) + ' ' + DATENAME(dd, Orders.OrderDate) + ', ' + DATENAME(yyyy, Orders.OrderDate) AS 'Order Date', Orders.delivered
      FROM Orders
      INNER JOIN Items
      ON Orders.itemID = Items.itemID
      ORDER BY Items.itemID ASC
  
  SELECT locationID, name AS 'Name', address, street, city, province, country, zip
      FROM Locations
  </code></pre>
  
  <p>In the Insert for the Orders Table, I have two orders for one Item and the same amount of quantity, but when the UPDATE statement is run, it only reflects one of them. I used an INNER JOIN on the UPDATE statement in hope it would match Orders.itemID to Items.itemID and insert the appropriate quantity into the appropriate spot. But unfortunatly, instead of it increasing by 12... It only Increases by 6.</p>
  
  <p>In Summary, It starts with</p>
  
  <pre><code>INSERT INTO Items
      (itemID, itemName, quantityOnHand, quantityOnOrder)
  VALUES
      (1, 'Mocha Java', 3, 0),
      (2, 'Stevia', 5, 0)
  </code></pre>
  
  <p>Removes the following</p>
  
  <pre><code>INSERT INTO Sales
      (invoiceID, itemID, quantity, location, InvoiceDate)
  VALUES
      (1, 1, 2, 1, GETDATE()),
      (2, 2, 1, 2, GETDATE())
  </code></pre>
  
  <p>And is supposed to update Items.quantityOnOrder with a SUM of all respective records in</p>
  
  <pre><code>INSERT INTO Orders
      (orderID, itemID, quantity, orderDate, delivered)
  VALUES
      (1, 1, 6, GETDATE(), 'N'),
      (2, 2, 2, GETDATE(), 'N'),
      (3, 1, 6, GETDATE(), 'N')
  </code></pre>
  
  <p>By using</p>
  
  <pre><code>UPDATE Items
      SET Items.quantityOnOrder = Items.quantityOnOrder + Orders.quantity
      FROM Items
      INNER JOIN Orders
      ON Items.itemID = Orders.itemID
  </code></pre>
  
  <p>Item 1 should have a quantityOnOrder of 12, but it shows 6.</p>
  
  <p>I hope I made it easy to understand as it's alot :)</p>
  
  <p>Any help would be greatly appreciated!!!</p>