sql server - UPDATE Using Self Join and Aggregates -

i'm trying update table using self join , aggregates.

for example, table has following columns:

store, item, price, lowprice, lowprice store

i need populate lowprice , lowprice store.

the lowprice be:

select item,min(price) group item 

the lowprice store store min(price) each item.

i'd love able take step further well. 2 stores offer item @ same lowprice. value lowprice store 'store a/store b' part less important.

i'm using sql server.

i agree @jnk comment better off using view. sql server 2005+ can try:

create view lowprices   select a.store, a.item, a.price, a.low_price, min(b.store) low_price_store (  select  *, min(price) over(partition item) low_price     yourtable) join yourtable b on a.low_price = b.price group a.store, a.item, a.price, a.low_price 

if still want update, try:

with cte (     select a.store, a.item, a.price, a.low_price, min(b.store) low_price_store     (  select  *, min(price) over(partition item) low_price             yourtable)     join yourtable b     on a.low_price = b.price     group a.store, a.item, a.price, a.low_price )  update set a.lowprice = b.low_price,     a.lowpricestore = b.low_price_store yourtable join cte b on a.store = b.store , a.item = b.item