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;

解决方法

我认为你不需要循环.我假设你的id是主键,你并不是要在你的例子中多次重复曲柄.

这样的东西会起作用吗？

根据Raj A的评论编辑.这将仅更新其他字段之一已更改的行.请注意,这不会更新其中一个为NULL而另一个不为NULL的行.

MERGE INTO final T 
USING ( SELECT id,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 ...]

sql – 更新MERGE语句ORACLE中的多个列

解决方法

猜你在找的MsSQL相关文章