SQL: update a field with a value from the same table, only if condition is met

Question

How to set each products' popularity (only when sales > 1) to that of the most popular product (from the same brand) in the same table. I have this so far:

UPDATE Products
SET popularity= 
    (
    SELECT TOP 1 popularity FROM products
    WHERE brand = currentRow.brand
    )
WHERE sales > 1

Obviously 'currentRow' can't exist since like this, so...

I was also looking into this answer, but no luck so far.

Would it just be better to iterate/loop over each row, check the condition, and update if necessary?

Answer 1

What you need here is alias:

UPDATE Tgt
SET popularity = (
    SELECT Max(popularity)
    FROM products Ref
    WHERE Ref.brand = Tgt.brand --< Refer by alias
)
FROM Products Tgt --< Give the target table an alias
WHERE sales > 1

Answer 2

Try this script:

SELECT TOP 1 POPULARITY, BRAND
INTO #TEMP
FROM PRODUCTS
WHERE SALES > 1

UPDATE PRODUCTS
SET POPULARITY = B.POPULARITY
FROM PRODUCTS A, #TEMP B
WHERE A.BRAND = B.BRAND

Answer 3

You can do this with a JOIN and a derived table

UPDATE P1
SET    P1.Popularity = P2.Popularity
FROM   Products P1
INNER JOIN (SELECT   MAX(Popularity) as [Popularity], Brand
            FROM     Products P2
            GROUP BY Brand) P2 on P2.Brand = P1.Brand
WHERE SALES > 1

Answer 4

If you use "TOP", you'll need to use ORDER BY, otherwise there is no guarantee that it will give you the most popular. If you are also looking for the most popular, only where sales are greater than 1, you would need a WHERE statement in your subquery. Using aliases as @Y.B. suggested, you could do something like this (with or without the WHERE in the subquery).

UPDATE a
SET popularity= 
    (
    SELECT TOP 1 b.popularity FROM Products b
    WHERE b.brand = a.brand 
    AND sales > 1
    ORDER BY b.popularity DESC
    )
FROM Products a
WHERE sales > 1