Update columns using conditions - Oracle SQL

Question

I have a table1:

u_a_id  d_c_s   c_nm    c_seq   r_c_p
1       908     Test1   1       20
10      908     Test1   1       21
11      908     Test1   1       12
12759   908     Test1   1       31
12759   908     Test1   1       32
12861   878     Test2   1       43
12861   878     Test2   1       44

I have SQL which finds the min r_c_p and max r_c_p which are repeated:

select u_a_id, d_c_s, c_nm, c_seq, count(*) cnt, min(r_c_p) min_rcp, max(r_c_p) max_rcp
from table1
where c_nm not in ('VOID', 'WRONG')
group by u_a_id, d_c_s, c_nm, c_seq
having count(*) > 1;

I need to update d_c_s and c_nm using output of above SQL as per following:

• for min(r_c_p) -- d_c_s = d_c_s + 12000 and c_nm = c_nm || '- 1'
• for max(r_c_p) -- d_c_s = d_c_s + 12000 and c_nm = c_nm || '- 2'

The output would be:

u_a_id  d_c_s   c_nm       c_seq    r_c_p
1       908     Test1      1        20
10      908     Test1      1        21
11      908     Test1      1        12
12759   12908   Test1 - 1  1        31
12759   12908   Test1 - 2  1        32
12861   12878   Test2 - 1  1        43
12861   12878   Test2 - 2  1        44

DB Fiddle - Link

Answer 1

You can use following Merge statement. Not sure about performance. But you can try this and then we can think of any performance tuning if required.

MERGE INTO TABLE1 T1 
USING (
       SELECT
           U_A_ID,
           D_C_S,
           C_NM,
           C_SEQ,
           MIN(R_C_P) MIN_RCP,
           MAX(R_C_P) MAX_RCP
       FROM
           TABLE1
       WHERE
           C_NM NOT IN (
               'VOID',
               'WRONG'
           )
       GROUP BY
           U_A_ID,
           D_C_S,
           C_NM,
           C_SEQ
       HAVING
           COUNT(1) > 1
   )
T2 ON ( T1.U_A_ID = T2.U_A_ID
        AND T1.C_SEQ = T2.C_SEQ )
WHEN MATCHED THEN UPDATE SET T1.D_C_S = T1.D_C_S + 12000,
T1.C_NM = CASE
    WHEN T1.R_C_P = T2.MIN_RCP THEN T1.C_NM || '- 1'
    ELSE T1.C_NM || '- 2'
END
WHERE
    T1.D_C_S = T2.D_C_S
    AND T1.C_NM = T2.C_NM
    AND T1.U_A_ID = T2.U_A_ID

DB Fiddle demo

Cheers!!