SolutionCottage.com
Posted  13 Jan 2009 - 09:34:40

Category: SQL


Update table fields based on a secondary temporary table.

In the following example I want to update the Stock on the Products using the temporary bulk imported table StockChange. How the Stock is changed is based on the ActionCode. 1 Means that Stock will be added. 2 Means that Stock will be removed. Here are the two update scripts needed to perform these actions:

UPDATE Product SET Product.Stock = Product.Stock + sc.Quantity
FROM Product
INNER JOIN _StockChange sc ON Product.ID = sc.ProductID
WHERE sc.ActionCode = 1

UPDATE Product SET Product.Stock = Product.Stock - sc.Quantity
FROM Product
INNER JOIN _StockChange sc ON Product.ID = sc.ProductID
WHERE sc.ActionCode = 2


Table Products:

ID Stock Title
1 10 Pants
2 15 Jackets
3 25 Shoes
4 40 Hats


Table StockChange:

ProductID Quantity ActionCode
2 10 1
2 5 2
4 10 1
3 2 2


So as you can see, on row one of StockChange we are adding a quantity of 10 to product with ID 2. On the second line we are removing 5 stock items from product with ID 2. Third line adds 10 to product with ID 4 and the fourth line removes 2 items from product with ID 3.








0 comments:








© SolutionCottage 2008-2011 7/26/2017 1:43:43 AM