oracle里merge多个字段,sql – 更新MERGE语句ORACLE中的多个列

我想在MERGE语句中更新多个列,但是对于每个列,条件都不同.我如何实现它.

我在这两个表中都有超过100万行.所有列都是number.Except Id所有3列的数字都精确到18位左右

例如：1.34255353433230675

是否有更好的Update.Around 50,000行可能每天更新所以我必须将更新值合并到目标表.

我试过UPDATE和FORALL,但速度很慢.

我基本上想要根据公共ID列合并差异.其他任何方法都更好？

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;

/