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

i've tried several options can't find right solution. want update 2 columns in products table. these columns need updated table called productshops.

the 2 columns named lowestprice , lowestpriceshopid. need lowest value sum newprice + deliverycosts in productshops , shopid record. needs updated in products table.

things i've tried are:

update products  set lowestprice = min(ps.newprice + ps.deliverycosts),  lowestpriceshopid = ps.shopid  products  inner join productshops ps on products.id = ps.productid 

error message : aggregate may not appear in set list of update statement.

update p  set lowestprice = ps2.totalprice, lowestpriceshopid = ps2.shopid products p join (select ps.productid, min(ps.newprice + ps.deliverycosts) totalprice, shopid productshops ps) ps2 on p.id = ps2.productid 

error message: column 'productshops.productid' invalid in select list because not contained in either aggregate function or group clause.

update products set lowestprice = (select min(ps.newprice + ps.deliverycosts)  productshops ps ps.productid = p.id) products p 

with last query don't know how shopid.

update p set lowestprice = ps2.totalprice,     lowestpriceshopid = ps2.shopid products p join (select     ps.productid,     min(ps.newprice + ps.deliverycosts) totalprice,     shopid productshops ps group ps.productid,shopid) ps2     on p.id = ps2.productid