Today, on another step of the job started with the last post, I had the following problem, I created a table with some informations and I needed to add more informations taken from other tables.
A an Example, I created an inventory table, with Article Code, Description, Quantity, to create it I used an INSERT query from the Articles table, then the users of my system inserted the Inventory values and now I need to add Sales Price and Cost of every article, reading them from the PriceList.
For Simplicity my tables are as follows:
TbInventory
IDInventory int
IDArticle nvarchar(32)
Quantity decimal(18,5)
Price decimal(18,5)
Cost decimal(18,5)
TbListini
IDPricelist int
IDarticle nvarchar(32)
Price decimal(18,5)
Cost decimal (18,5)
To update price and cost I’lluse, an Update query, with a Join.
UPDATE TbInventory
SET Price = isnull(lis.Price,0)
, Cost = isnull(lis.Cost,0)
FROM TbInventory inv
INNER JOIN
TbPriceList lis
ON
inv.IDArticle = lis.IDArticle
This way, I’m setting a relation between the two tables usin ga JOIN thenI update the Inventory Table with The Price List values.