MERGE - 不匹配时 INSERT 的异常

发布于 2024-09-16 11:49:55 字数 521 浏览 8 评论 0原文

我有一个使用 MERGE 的 PL/SQL 过程：

MERGE INTO 
  table_dest d
USING
  (SELECT * FROM my_Table) s
ON
  (s.id = d.id)
when matched     then UPDATE set d.col1 = s.col1
when not matched then INSERT (id, col1) values (s.id, s.col1);

现在假设查询 s 返回具有相同 id 的多行，这将返回

我想要的 ORA-00001: unique constrain error要做的是将重复的列发送到另一个表 my_Table_recyledbin 以获得成功的 INSERT，我可以使用 EXCEPTION WHEN DUP_VAL_ON_INDEX 吗？如果是，如何将其与 MERGE 语句一起使用？

提前致谢

原文

i have a PL/SQL procedure using MERGE :

MERGE INTO 
  table_dest d
USING
  (SELECT * FROM my_Table) s
ON
  (s.id = d.id)
when matched     then UPDATE set d.col1 = s.col1
when not matched then INSERT (id, col1) values (s.id, s.col1);

now lets say the query s returns mutiple rows with same id wich will returns an ORA-00001: unique constrain error

what i want to do is to send the duplicated columns to another table my_Table_recyledbin to get a successful INSERT, can i use EXCEPTION WHEN DUP_VAL_ON_INDEX ? if yes how to use it with the MERGE statement?

Thanks in advance

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

跨年 2024-09-23 11:49:55

为什么不在单独的语句中处理将重复行归档到回收站表的问题？

首先，进行合并（聚合重复行以避免唯一约束错误）。我假设 col1 上有一个 MAX 聚合函数，但您可以使用适合您需要的任何函数 - 您尚未指定如何决定在存在重复项时使用哪一行。

MERGE INTO 
  table_dest d
USING
  (SELECT a.id, MAX(a.col1) as col1
     FROM my_Table a
    GROUP BY a.id) s
ON
  (s.id = d.id)
WHEN MATCHED THEN UPDATE SET d.col1 = s.col1
WHEN NOT MATCHED THEN INSERT (id, col1) VALUES (s.id, s.col1);

然后，处理重复的行。我假设您的回收站表确实允许插入重复的 id：

INSERT INTO my_Table_recyledbin r (id, col1)
SELECT s.id, s.col1
  FROM my_Table s
 WHERE EXISTS (SELECT 1
                 FROM my_Table t
                WHERE t.id = s.id
                  AND t.ROWID != s.ROWID)

希望这能够满足您的需求。

Why not handle the archiving of duplicate rows to the recycle bin table in a separate statement?

Firstly, do your merge (aggregating the duplicate rows to avoid the unique constraint error). I've assumed a MAX aggregate function on col1, but you can use whatever suits your needs -- you have not specified how to decide which row to use when there are duplicates.

MERGE INTO 
  table_dest d
USING
  (SELECT a.id, MAX(a.col1) as col1
     FROM my_Table a
    GROUP BY a.id) s
ON
  (s.id = d.id)
WHEN MATCHED THEN UPDATE SET d.col1 = s.col1
WHEN NOT MATCHED THEN INSERT (id, col1) VALUES (s.id, s.col1);

Then, deal with the duplicate rows. I'm assuming that your recycle bin table does allow duplicate ids to be inserted:

INSERT INTO my_Table_recyledbin r (id, col1)
SELECT s.id, s.col1
  FROM my_Table s
 WHERE EXISTS (SELECT 1
                 FROM my_Table t
                WHERE t.id = s.id
                  AND t.ROWID != s.ROWID)

Hopefully, that should fulfil your needs.

回复收藏 0 原文