Update records based on select query

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 another table2:

d_c_s   c_nm    c_seq   n_min_d_c_s     n_min_c_nm
908     Test1   1       12001           Test1, First
878     Test2   1       12002           Test2, First

I need to update table1 ==>

table1.c_nm = table2.n_min_c_nm
table1.d_c_s = table2.n_min_d_c_s

The condition to update would be:

ONLY records where count of (u_a_id, d_c_s, c_seq) > 1 -- select u_a_id, d_c_s, c_nm, c_seq, count(*) cnt from table1 where c_nm not in ('VOID', 'WRONG') group by u_a_id, d_c_s, c_nm, c_seq having count(*) > 1;
table1.d_c_s = table2.d_c_s
table1.c_nm = table2.c_nm
table1.c_seq = table2.c_seq
Use min of r_c_p

The output would look like:

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   12001   Test1, First    1       31
12759   908     Test1           1       32
12861   12002   Test2, First    1       43
12861   878     Test2           1       44

What would be the best way to create UPDATE/MERGE query to make this happen?

DBFIDDLE DEMO - Link

Popeye · Accepted Answer · 2019-06-17T08:31:07.463

1

You can use MERGE statement for the same as the following:

MERGE INTO TABLE1 T1 
USING (
       SELECT
           MIN(TI1.R_C_P) AS R_C_P,
           TI1.U_A_ID,
           TI1.D_C_S,
           TI1.C_NM,
           TI1.C_SEQ,
           TI2.N_MIN_C_NM,
           TI2.N_MIN_D_C_S
       FROM
           TABLE1 TI1
           JOIN TABLE2 TI2 
           ON ( TI1.D_C_S = TI2.D_C_S
                AND TI1.C_NM = TI2.C_NM
                AND TI1.C_SEQ = TI2.C_SEQ
                AND TI1.C_NM NOT IN (
               'VOID',
               'WRONG'
           ) )
       GROUP BY
           TI1.U_A_ID,
           TI1.D_C_S,
           TI1.C_NM,
           TI1.C_SEQ,
           TI2.N_MIN_C_NM,
           TI2.N_MIN_D_C_S
       HAVING
           COUNT(1) > 1
   )
T2 ON ( T1.C_SEQ = T2.C_SEQ
        AND T1.R_C_P = T2.R_C_P )
WHEN MATCHED THEN 
UPDATE 
SET T1.C_NM = T2.N_MIN_C_NM,
T1.D_C_S = T2.N_MIN_D_C_S
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 -- ADDED THIS CONDITION
-- WHERE CONDITION IS USED AS IT CAN NOT BE USED INSIDE ON CLAUSE
-- TO AVOID ERROR : ORA-38104: Columns referenced in the ON Clause cannot be updated

Demo DB Fiddler

Cheers!!

edited Jun 17 '19 at 08:31

answered Jun 17 '19 at 05:39

Popeye

35,427
4
10
31

It is giving wrong output if I change R_C_P for the first row to 43 -- https://dbfiddle.uk/?rdbms=oracle_18&fiddle=4732cc0ae88ecd0f281adb57ab1c8f37 – dang Jun 17 '19 at 06:09
Yes, I was trying to put some unique columns in WHERE clause. Now I have put `U_A_ID` in it so that it can identify the correct record. If `Table1` has PK then use only that column in `WHERE` clause.. **Please see the updated answer with updated fiddler link** – Popeye Jun 17 '19 at 06:36
Is this line -- T2 ON ( T2.C_SEQ = T2.C_SEQ -- correct? – dang Jun 17 '19 at 08:26
Ohh.. Nope, That was not correct. It is corrected now. – Popeye Jun 17 '19 at 08:30
Would it cause any issue because of it? – dang Jun 17 '19 at 08:38
Nope. It will not. – Popeye Jun 17 '19 at 11:17

Update records based on select query

1 Answers1