UPDATE Using Self Join and Aggregates

I'm trying to update a table using a self join and aggregates.

For example, a tabl开发者_如何学JAVAe has the following columns:

store, item, price, lowprice, lowprice store

I need to populate lowprice and lowprice store.

The lowprice would be:

Select item,min(price) group by item

The lowprice store would be the store with the min(price) for each item.

I'd love to be able to take it a step further as well. Say two stores offer an item at the same lowprice. Then the value for lowprice store would be 'store a/store b' but this part is less important.

I'm using SQL Server.

I agree with @JNK comment that you are better off using a VIEW. For SQL Server 2005+ you can try:

CREATE VIEW LowPrices 
AS

SELECT A.store, A.item, A.price, A.Low_Price, MIN(B.store) Low_Price_Store
FROM (  SELECT  *, MIN(price) OVER(PARTITION BY item) Low_Price
    FROM YourTable) A
JOIN YourTable B
ON A.Low_Price = B.price
GROUP BY A.store, A.item, A.price, A.Low_Price

If you still want to do the UPDATE, then try:

WITH CTE AS
(
    SELECT A.store, A.item, A.price, A.Low_Price, MIN(B.store) Low_Price_Store
    FROM (  SELECT  *, MIN(price) OVER(PARTITION BY item) Low_Price
            FROM YourTable) A
    JOIN YourTable B
    ON A.Low_Price = B.price
    GROUP BY A.store, A.item, A.price, A.Low_Price
)

UPDATE A
SET A.LowPrice = B.Low_Price,
    A.LowPriceStore = B.Low_Price_Store
FROM YourTable A
JOIN CTE B
ON A.store = B.Store AND A.item = B.item