Update query Pass through

[Tiger955]

Hi!

Please help me in this issue as I do not know how to handle it:

A PT query from Access 2010 to SQL server 2012 for an inventory project.

tbls:
tblArticles, column ArtID, column Price
tblMovementsdetails, column ArtID, column Number, column Unitprice

Whenever new items (ArtID) from an invoice are inserted in tblMovementsdetails I have to calculate a new average price "Sum([Number]*[Unitpreis])/Sum([Number])" from ALL items in tblMovementsdetails per ArtID and update this new average price in tblArticles for each individual ArtID.

With the statement below ALL items get the same price in tblArticles.

How is the correct statement for the average price for every ArtID?

UPDATE dbo.tblArticles Set Price= u1.Average FROM dbo.tblMovementsdetails As A INNER JOIN (SELECT B.ArtID, Sum([Number]*[Unitpreis])/Sum([Number]) AS Average FROM tblMovementsdetails AS B INNER JOIN tblArticles ON tblArticles.ArtID = B.ArtID GROUP BY B.ArtID) as u1 On A.ArtID = u1.ArtID;

I could do it with x recordsets, looping through the tbls, but I think that above way would be the faster solution - when it works!

Thanks a lot for your help?
Michael

 

[WayneRyan]

Michael,

You can make a view:

Create view dbo.GetAveragePrice
AS
Select Avg([Number] * [UnitPrice]) As AveragePrice
From   tblMovementsdetails
Group By ArtID

Then you can always join to the view to get the current AveragePrice.

Select a.ArtID, b.AveragePrice
From   tblArticles as a Inner Join dbo.GetAveragePrice as b on
          a.ArtID = b.ArtID

Don't do this:

If you insist on maintaining the value in tblArticles it will get more complex.
You'll have to Update tblArticles:

Update tblArticles
Set    tblArticles.Price = GetAveragePrice.AveragePrice
From   tblArticles, GetAveragePrice
Where  tblArticles.ArtID = GetAveragePrice.ArtID

Then, you'll to use the Insert AND Update triggers to use the Update SQL above
to keep the values current.

Then, you'll have to ensure that NOBODY ever changes the Price value in
tblArticles.

It is WAY easier to just make the view and use it to retrieve the current
average price when you need it.

Wayne

 

[Tiger955]

Thanks a lot!

Its definitively the better way, either I decide to update tblArticles or keep it dynamic with only the view.
Michael