Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Update multiple columns in MERGE statement ORACLE

I want to update multiple columns in MERGE statement,but for each column the conditions are different.How can I achieve it.

I have more than 1 million rows in both the tables.All columns are number.Except Id all 3 columns have number with precision around 18 digits eg: 1.34255353433230675

Is there a better way to Update.Around 50,000 rows might update daily So I have to merge the updates values to target table. I tried UPDATE and FORALL but its slow.

I basically want to merge the difference based on common ID column.Any other approach is 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;

/

like image 658
Rajiv A Avatar asked Jun 14 '13 22:06

Rajiv A


People also ask

How update multiple columns in Oracle join?

If you need to update multiple columns simultaneously, use comma to separate each column after the SET keyword.

Can we update multiple columns in a single update statement in Oracle?

Note that the UPDATE statement allows you to update as many columns as you want.

How can you improve the performance of a MERGE statement in Oracle?

I would recommend enabling row source statistics, executing the same statement with same source data, and extracting the plan with row source execution statistics.

Which is better MERGE or update in Oracle?

merge is faster for merging. update is faster for updating.


1 Answers

I don't think you need the loop. I'm assuming your id's are primary keys and you didn't mean to repeat crank several times in your example.

Would something like this work?

Edit per Raj A's comment. This will only update rows where 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 ...]
like image 140
eaolson Avatar answered Nov 15 '22 05:11

eaolson