Update a column using another column in the same table with two entries (Values) in SQL Server

I’m trying to update a column Product_Group from a column called Product in the same table. This Product column has two entries, Simple or Others. i.e shown below in Invoice table:

The issue I’m have is that if you look at Top_Level ID 10, 19, 21 and 31 has multiple entries and the Product are multiple as well (i.e Simple and Others).

What I want to achieve is that when I see Top_Level who has Simple and Others then update Product_Group Column to “Simple/Other”

How can I achieve this?

Answer

You have only two groups, so you can use window functions, min() and max():

select i.*,
       (case when min(product) over (partition by top_level) =
                  max(product) over (partition by top_level)
             then product
             else 'Other/Simple'
        end) as product_group
from invoices i;

You would want to use min(product) over (partition by top_level) if product could ever be NULL.

This approach does not generalize (easily) to more than two groups. But it should work well in this situation.

EDIT:

If you actually want to update the column, just use a CTE:

with toupdate as (
      select i.*,
             (case when min(product) over (partition by top_level) =
                        max(product) over (partition by top_level)
                   then product
                   else 'Other/Simple'
              end) as new_product_group
      from invoices i
     )
update toupdate
    set product_group = new_product_group;