CODEWITHSUNDEEP
sqlsql-server
Tim
Tim

Reputation: 51

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:

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"

Final outcome

How can I achieve this?

Upvotes: 3

Views: 64

Answers (2)

Lukasz Szozda
Lukasz Szozda

Reputation: 175706

If you have only 2 Product then you could use:

WITH cte AS (
   SELECT Top_Level, COUNT(DISTINCT Product) AS cnt
   FROM tab
   GROUP BY Top_Level
)
UPDATE t
SET Product_group = CASE WHEN cnt = 1 THEN t.Product ELSE 'Others/Simple' END;
FROM tab t
JOIN cte ctab
  ON c.Top_Level = t.Top_Level;

Upvotes: 2

Gordon Linoff
Gordon Linoff

Reputation: 1269773

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;

Upvotes: 0

Related Questions