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

  • SQL Select with Group By and Order By Date
  • SQL count, group by, and retrieve related data from another table
  • T-SQL script to calculate time difference between multiple rows
  • How to select a single element from each VIEW in Microsoft SQL 2008
  • Using Full-Text indexing to crawl binary blobs
  • Limit NLog Database Target Size
  • 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

    One Solution collect form web for “Update Query with Condition in SQL”

    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
    
    MS SQL Server is a Microsoft SQL Database product, include sql server standard, sql server management studio, sql server express and so on.