Updating Rows in a normalized table

Question

normalized table

ID SEQ Type Value Flag
1  1   a    100   -
1  2   a    200   -
1  3   a    250   -
1  4   b    200   -
2  1   a    150   -
2  2   b    100   -
2  3   b    200   -

How do I write a single update statement such that the resulting table is populated as follows

ID SEQ Type Value Flag
1  1   a    100   valid
1  2   a    200   repeat
1  3   a    250   repeat
1  4   b    200   valid
2  1   a    150   valid
2  2   b    100   valid
2  3   b    200   repeat

Edit: included seq column

only the first occurence of the value for a type for a ID group should have the valid flag should it be written as two separate update statements? can someone clarify me? Much appreciated

Answer 1

Populate the table first using row_number() and then update the table.

Option 1:

select
    Id,
    Type,
    Value,
    null as Flag,
    row_number() over (partition by ID, Type order by SEQ) as rnk
from yourTable

then you can use update

update yourTable
set flag = case
                when rnk = 1 then 'valid'
                else 'repeat'
            end

Option 2: You may be able to do without using update statement as following

select
    Id,
    SEQ,
    Type,
    Value,
    case
        when rnk = 1 then 'valid'
        else 'repeat'
    end as flag
from
(
    select
        Id,
        SEQ,
        Type,
        Value,
        row_number() over (partition by ID, Type order by SEQ) as rnk
    from yourTable
) val