在MERGE语句ORACLE中更新多个列 [英] Update multiple columns in MERGE statement ORACLE

问题描述

我想更新MERGE语句中的多个列，但是对于每个列，条件不同。我如何实现它。

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

我有超过100万行在所有的表中。所有列是number.Except Id所有3列的数字精度约为18位数
例如：1.34255353433230675

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

有更好的方法更新。每行50,000行可能更新所以我必须将更新值合并到目标表。
我试过UPDATE和FORALL但是它的速度慢。

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.

我基本上想合并基于公共ID列的差异。 >

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;

/

推荐答案

我不认为你需要循环。我假设你的id是主键，你不想在你的例子中重复几次。

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.

这样的东西能工作吗？

根据Raj A的评论进行编辑。这将只更新其中一个其他字段已更改的行。注意，这不会更新其中一个为NULL而另一个不为NULL的行。

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 ...]

这篇关于在MERGE语句ORACLE中更新多个列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！