How to update two columns in a table with an aggregate function in MSSQL?

Question

I've tried several options but I can't find the right solution. I want to update two columns in the Products table. These columns need to be updated from another table called ProductShops.

The two columns are named LowestPrice and LowestPriceShopId. So I need the lowest value from the sum NewPrice + DeliveryCosts in ProductShops and the ShopId from that record. That needs to be updated in the Products table.

Things that I've tried are:

UPDATE Products 
SET LowestPrice = MIN(ps.NewPrice + ps.DeliveryCosts), 
LowestPriceShopId = ps.ShopId 
FROM Products 
INNER JOIN ProductShops ps ON Products.Id = ps.ProductId

Error message : An aggregate may not appear in the set list of an UPDATE statement.

UPDATE p 
SET LowestPrice = ps2.totalPrice,
LowestPriceShopId = ps2.ShopId
FROM Products p JOIN
(select ps.ProductId, MIN(ps.NewPrice + ps.DeliveryCosts) AS totalPrice, ShopId FROM ProductShops ps) ps2
ON p.Id = ps2.ProductId

Error message: Column 'ProductShops.ProductId' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

UPDATE Products
SET LowestPrice = (SELECT MIN(ps.NewPrice + ps.DeliveryCosts) 
FROM ProductShops ps WHERE ps.ProductId = p.Id)
FROM Products p

With the last query I don't know how to get the ShopId.

score 3 · Answer 1 · answered Jun 04 '15 at 10:09

UPDATE p
SET LowestPrice = ps2.totalPrice,
    LowestPriceShopId = ps2.ShopId
FROM Products p
JOIN (SELECT
    ps.ProductId,
    MIN(ps.NewPrice + ps.DeliveryCosts) AS totalPrice,
    ShopId
FROM ProductShops ps
GROUP BY ps.ProductId,ShopId) ps2
    ON p.Id = ps2.ProductId

score 2 · Answer 2 · answered Jun 05 '15 at 09:05

I found the right solution:

UPDATE p 
SET LowestPrice = ps3.totalPrice,
LowestPriceShopId = ps3.ShopId
FROM Products p 
JOIN
(
SELECT ps1.ProductId, ps1.NewPrice + ps1.DeliveryCosts as totalPrice, ps1.ShopId FROM ProductShops ps1 
LEFT JOIN ProductShops ps2
ON ps1.ProductId = ps2.ProductId AND (ps1.NewPrice + ps1.DeliveryCosts) > (ps2.NewPrice + ps2.DeliveryCosts)
WHERE ps2.NewPrice is NULL AND ps2.DeliveryCosts is NULL
) ps3
ON p.Id = ps3.ProductId

Arion · Answer 3 · 2015-06-04T11:43:11.960

1

You need to GROUP BY:

UPDATE p 
SET LowestPrice = ps2.totalPrice,
LowestPriceShopId = ps2.ShopId
FROM Products p 
JOIN
(
    select 
        ps.ProductId, 
        MIN(ps.NewPrice)+MIN(ps.DeliveryCosts) AS totalPrice, 
        ShopId 
    FROM 
        ProductShops ps
    GROUP BY
        ps.ProductId,
        ps.ShopId
) ps2
ON p.Id = ps2.ProductId

On both ProductId and ShopId

edited Jun 04 '15 at 11:43

answered Jun 04 '15 at 10:09

Arion

31,011
10
70
88

I have 2 ProductShops records with the same ProductId, but if i run the query the higest price is added to the Product record. Do you know how this can happen? – Pim Jun 04 '15 at 11:20
@Pim : I updated the answer. It might be that the total of `ps.NewPrice + ps.DeliveryCosts` is higher then `NewPrice` and `DeliveryCosts` individual. So test the query like this – Arion Jun 04 '15 at 11:44

How to update two columns in a table with an aggregate function in MSSQL?

3 Answers3