CODEWITHSUNDEEP
sqlsql-servert-sql
moe
moe

Reputation: 5249

Update Query with Condition in SQL

I have a table that has 2 columns and i am trying to update another table based on these criteria:

  1. Set the flag to 'Good' for the most duplicate keys in the Main_Key column for the same GROUP_KEY (Note we can have different Main_Keys for any GROUP_KEY)
  2. Set the flag to 'Bad' for the least duplicate keys in the Main_Key column for the same GROUP_KEY
  3. Set the flag to 'Don't Use' if the different Main_Keys are equal for the same GROUP_KEY

HERE IS MY TABLE

GROUP_KEY   MAIN_KEY
22            4
22            4
22           55
22           55
22           55
22           55
10           10
10           10
18           87
18           22
18           22

HERE IS THE DESIRED RESULT AFTER THE UPDATE

GROUP_KEY   MAIN_KEY           FLAG
    22            4          Bad
    22            4          bad
    22           55          Good
    22           55          Good
    22           55          Good
    22           55          Good
    10           10          Don't Use
    10           10          Don't Use
    18           87          Bad
    18           22          Good
    18           22          Good

I only know how to do just normal update query but not where even to start this logic. thnx for the help

Upvotes: 1

Views: 161

Answers (1)

Kirill Polishchuk
Kirill Polishchuk

Reputation: 56222

Use:

declare @t table(GROUP_KEY int, MAIN_KEY int)

insert @t values
(22, 4),
(22, 4),
(22, 55),
(22, 55),
(22, 55),
(22, 55),
(10, 10),
(10, 10),
(18, 87),
(18, 22),
(18, 22)

select t.*, b.flag
from @t t
join
(
    select a.GROUP_KEY, a.MAIN_KEY
        , 
            case
                when a.GROUP_KEY = a.MAIN_KEY
                    then 'Don''t Use'
                when a.count = MAX(a.count) over(partition by a.GROUP_KEY)
                    then 'Good'
                else 'Bad'
            end [flag]
    from
    (
        select t.GROUP_KEY, t.MAIN_KEY, COUNT(*) [count]
        from @t t
        group by t.GROUP_KEY, t.MAIN_KEY
    )a
)b
on b.GROUP_KEY = t.GROUP_KEY and b.MAIN_KEY = t.MAIN_KEY

Output:

GROUP_KEY   MAIN_KEY    flag
----------- ----------- ---------
10          10          Don't Use
10          10          Don't Use
18          22          Good
18          22          Good
18          87          Bad
22          4           Bad
22          4           Bad
22          55          Good
22          55          Good
22          55          Good
22          55          Good

Update: Assuming you have flag column in your table:

update @t
set flag = b.flag
from @t t
join
(
    select a.GROUP_KEY, a.MAIN_KEY
        , 
            case
                when a.GROUP_KEY = a.MAIN_KEY
                    then 'Don''t Use'
                when a.count = MAX(a.count) over(partition by a.GROUP_KEY)
                    then 'Good'
                else 'Bad'
            end [flag]
    from
    (
        select t.GROUP_KEY, t.MAIN_KEY, COUNT(*) [count]
        from @t t
        group by t.GROUP_KEY, t.MAIN_KEY
    )a
)b
on b.GROUP_KEY = t.GROUP_KEY and b.MAIN_KEY = t.MAIN_KEY

Upvotes: 2

Related Questions