Use group by/having in Update statement-CodePudding

I have to get the earliest dt grouped as per id and indicator and then update that in the earliest_dt table

(select min(start_dt) as earliest_dt from test_table group by id,indicator)

in an update statement.

Sample Table Test_table

    id   start_dt  earliest_dt  indicator
    111    20/02                  1
    111    15/02                  1
    111    14/02                  1
    111    25/02                  1
    111    23/02                  2
    111    07/02                  2
    222    10/02                  1
    222    22/02                  1
    222    25/02                  1
    222    12/02                  2
    333    22/02                  1
    333    01/02                  1
    333    22/02                  2
    333    05/02                  2

Result Table

    id   start_dt  earliest_dt  indicator
    111    20/02        14/02      1
    111    15/02        14/02      1
    111    14/02        14/02      1
    111    25/02        14/02      1
    111    23/02        07/02      2
    111    07/02        07/02      2
    222    10/02        10/02      1
    222    22/02        10/02      1
    222    25/02        10/02      1
    222    12/02        12/02      2
    333    22/02        01/02      1
    333    01/02        01/02      1
    333    22/02        05/02      2
    333    05/02        05/02      2

CodePudding user response：

Use the min window function and optionally an updatable CTE.

with cte as (
    select id, start_dt, earliest_dt, indicator
        , min(start_dt) over (partition by id, indicator) as new_earliest_dt
    from MyTable
)
update cte set earliest_dt = new_earliest_dt;