Updating multiple columns in a MERGE ORACLE statement

Question

I want to update several columns in a MERGE statement, but the conditions are different for each column. How can i achieve this.

I have over 1 million rows in both tables. All columns are number.Except Id all 3 columns have a number with an accuracy of about 18 digits for example: 1.34255353433230675

Is there a better way to upgrade. About 50,000 rows can be updated daily. Therefore, I need to combine the update values ​​into a target table. I tried UPDATE and FORALL, but slow.

Basically, I want to combine the difference based on a common identifier column. Is any other approach better?

DECLARE TYPE test1_t IS TABLE OF test.score%TYPE INDEX BY PLS_INTEGER; TYPE test2_t IS TABLE OF test.id%TYPE INDEX BY PLS_INTEGER; TYPE test3_t IS TABLE OF test.Crank%TYPE INDEX BY PLS_INTEGER; TYPE test4_t IS TABLE OF test.urank%TYPE INDEX BY PLS_INTEGER; vscore test1_t; vid test2_t; vcrank test3_t; vurank test4_t; BEGIN SELECT id,score,crank,urank BULK COLLECT INTO vid,vscore,vcrank,vurank FROM test; FORALL i IN 1 .. vid.COUNT MERGE INTO final T USING (SELECT vid (i) AS o_id, vcrank (i) AS o_crank, vurank (i) AS o_urank vscore (i) AS o_score FROM DUAL) S ON (S.o_id = T.id) WHEN MATCHED THEN UPDATE SET T.crank = S.o_crank WHERE T.crank <> S.o_crank; UPDATE SET T.crank = S.o_crank WHERE T.crank <> S.o_crank; UPDATE SET T.crank = S.o_crank WHERE T.crank <> S.o_crank; UPDATE SET T.score = S.score WHERE T.score <> S.score; -- I tried the below case its not working either... -- UPDATE SET T.crank = (CASE WHEN T.crank <> S.o_crank -- THEN S.o_crank -- END), -- T.urank = (CASE WHEN T.urank <> S.o_urank -- THEN S.o_urank -- END); COMMIT; END; 

/

Answer 1

I do not think you need a loop. I assume that your identifier is the primary key, and you did not want to repeat the crank several times in your example.

Something like this work?

Edit for Raj Comment. This will only update rows in which one of the other fields has changed. Note that this will not update rows where one is NULL and the other is not NULL.

 MERGE INTO final T USING ( SELECT id, score, crank, urank FROM test ) S ON ( S.vid = T.id AND ( S.crank != T.crank OR S.score != T.score OR S.urank != T.urank )) WHEN MATCHED SET crank = S.crank, score = S.score, crank = S.crank, urank = S.urank WHEN NOT MATCHED THEN INSERT [... not sure what you want to do in this case ...]