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.

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