更新 MERGE 语句 ORACLE 中的多列

2021-12-24 00:00:00 sql database oracle plsql

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

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

我在两个表中都有超过 100 万行.所有列都是数字.除了 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.

这样的东西会起作用吗?

Would something like this work?

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

相关文章