full_prog_full full_prog_full - 1 year ago 58
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 Source

What you need here is alias:

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
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download