Update Query with Condition in SQL

Question

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

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)
Set the flag to 'Bad' for the least duplicate keys in the Main_Key column for the same GROUP_KEY
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

thanks for asking, because i only consider Good when you have the most duplicate number in the Main_Key for the same Group_Key. If you look at it, the number 22 shows up twice and and 87 shows up only one time for the same group key. — moe
– moe, Commented Oct 21, 2013 at 23:57

Kirill Polishchuk · Accepted Answer · 2013-10-22 00:53:12Z

2

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

edited Oct 22, 2013 at 0:53

answered Oct 22, 2013 at 0:17

Kirill Polishchuk

56.4k11 gold badges125 silver badges127 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

moe Over a year ago

thanks this is really helpful, is it possible to change this as an update query? i am trying to understand the syntax where you insert and declare because i already have the table and would like to just update it. thnx

moe Over a year ago

it is failing the @t so that is why i was trying to simplify just to use an update query since i already have the table with flag column. thnx

Kirill Polishchuk Over a year ago

@moe, I've updated my answer. Instead of @t use your table name.

Collectives™ on Stack Overflow

Update Query with Condition in SQL

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related