Update multiple columns from a different table selecting only the latest record
I have a table tblProducts and a table tblProductPricing. There might be several prices for each product. I now would like to update table products with the latest prices in tblProductPricing. I currently have following SQL:
Update Products a INNER JOIN tblProductPricing b ON a.ProviderId=b.ProviderId AND a.ProductId=b.ProductID AND a.PriceInd=b.PriceInd
Set a.ValidDate =b.ValidDate, a.PriceUsd= b.PriceUsd, a.PriceLoc = b.PriceLoc
However, I am not sure how to include only the latest price (ValidDate) using max(validDate)? Thank you so much for any help. Best regards Michael
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
You may have to insert first without the price, then run an update of the PriceLoc.
ASKER
Thank you so much for all your help.. Cheers Michael
ASKER
INSERT INTO tmpPricing
(bCompanyId, ProductID,ProviderId,CatCode,ProdFamily,PriceInd,PriceLoc)
SELECT 10, ProductId,106,CatCode,ProdFamily,1,
(Select Top 1 t.PriceLoc FROM tblProductPricing t WHERE t.ProviderID=tblProducts.ProviderID AND t.ProductId=tblProducts.ProductId AND t.PriceInd=1) AS CurrentPrice
FROM tblProducts WHERE Catcode= 200 AND ProdFamily=7 AND Not Cancelled
This works fine. However, when I try to add the order by clause it gives me an error:
INSERT INTO tmpPricing
(bCompanyId, ProductID,ProviderId,CatCode,ProdFamily,PriceInd,PriceLoc)
SELECT 10, ProductId,106,CatCode,ProdFamily,1, (Select Top 1 t.PriceLoc FROM tblProductPricing t WHERE t.ProviderID=tblProducts.ProviderID AND t.ProductId=tblProducts.ProductId AND t.PriceInd=1 Order By tblProductPricing.Validdate DESC) AS CurrentPrice
FROM tblProducts WHERE Catcode= 200 AND ProdFamily=7 AND Not Cancelled
Not sure why? The error msg is that at most one record may me returned by the subquery.. Any suggestions would be greatly appreciated.... Tx Michael