CODEWITHSUNDEEP
sqloracle-databasesql-merge
dang
dang

Reputation: 2412

Update columns using conditions - Oracle SQL

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:

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

Upvotes: 0

Views: 127

Answers (3)

Gordon Linoff
Gordon Linoff

Reputation: 1269663

You can use merge:

merge into table1 t1
    using (select tt1.u_a_id, tt1.d_c_s, t1.c_nm, tt1.c_seq, 
                  min(tt1.r_c_p) as min_r_c_p,
                  max(tt1.r_c_p) as max_r_c_p
           from table1 tt1
           where tt1.c_nm not in ('VOID', 'WRONG')
           group by tt1.u_a_id, tt1.d_c_s, t1.c_nm, tt1.c_seq
           having count(*) >= 2
          ) tt1
          on (tt1.u_a_id = t1.u_a_id and
              tt1.d_c_s = t1.d_c_s and
              tt1.c_nm = t1.c_nm and
              tt1.c_seq = t1.c_seq and
              tt1.r_c_p in (min_r_c_p, max_r_c_p)
             )
    when matched then
        set d_c_s = d_c_s + 12000,
            c_nm = (case when tt1.r_c_p = min_r_c_p then c_nm || '- 1'
                         when tt1.r_c_p = max_r_c_p then 
c_nm || '- 2'
                         else tt1.r_c_p
                    end);

For performance, I would recommend an index on (u_a_id, d_c_s, c_nm, c_seq, r_c_p).

Upvotes: 0

Popeye
Popeye

Reputation: 35900

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!!

Upvotes: 1

forpas
forpas

Reputation: 164089

You must join your query to the table and apply the conditions in CASE statements:

select 
  t.u_a_id,
  case when g.u_a_id is null then t.d_c_s else 12000 + t.d_c_s end d_c_s,  
  case 
    when t.r_c_p = g.min_rcp then t.c_nm || '-1'
    when t.r_c_p = g.max_rcp then t.c_nm || '-2'
    else t.c_nm
  end c_nm,
  t.c_seq, 
  t.r_c_p
from table1 t left join (
  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
) g on 
  g.u_a_id = t.u_a_id and g.d_c_s = t.d_c_s and g.c_nm = t.c_nm and g.c_seq = t.c_seq
  and t.r_c_p in (g.min_rcp, g.max_rcp)

See the demo.
Results:

> 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 | 12908 | Test1-1 |     1 |    43
>  12861 | 12908 | Test1-2 |     1 |    44

Upvotes: 1

Related Questions