full_prog_full full_prog_full - 6 months ago 9
SQL Question

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

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

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
Comments