合并更新oracle无法获得稳定的行集 [英] merge update oracle unable to get a stable set of rows

问题描述

我正在尝试基于另一个表(内部联接)更新Oracle中的表，但是匹配的字段在多于1行上匹配，所以出现错误:

I am trying to update a table in Oracle based on another table (inner join) but the matching fields match on more than 1 row so I get an error:

无法获得稳定的行集

unable to get a stable set of rows

    MERGE INTO C
    USING D
    ON (C.SYSTEM = D.SYSTEM)
    WHEN MATCHED THEN
    UPDATE SET C.REF_CD = D.CODE,
               C.REF_DT = TO_DATE('12/05/2017', 'MM/DD/YYYY')
       WHERE C.CODE = '123'
       AND D.CODE IS NOT NULL
       AND C.CLOSED = 'N'
       AND C.RCVD_DT >= TO_DATE('12/01/2017', 'MM/DD/YYYY')
       AND C.RCVD_DT <= TO_DATE('12/04/2017', 'MM/DD/YYYY')
       AND SUBSTR(C.SSN,7,3) >= D.FROM
       AND SUBSTR(C.SSN,7,3) <= D.TO;

作为SELECT语句，我可以使用内部联接提取此信息.但是作为合并语句，我得到了上面的错误. SYSTEM是两个表中唯一匹配的数据.如何以不会出错的方式重写上面的内容?

As a SELECT statement, I can pull this information using an inner join. But as a merge statement, I get the above error. SYSTEM is the only matching data in both tables. How can I rewrite the above in a way that it will not error out?

推荐答案

我将显示此错误的根源.
考虑下面的简单示例:

I am going to show what the source of this error is.
Consider the below simple example:

CREATE TABLE A_100(
  x_system int,
  val int
);

INSERT INTO a_100 values( 1, 100 );
INSERT INTO a_100 values( 2, 200 );

CREATE TABLE B_100(
  x_system int,
  val int
);

INSERT INTO b_100 values( 1, 1100 );
INSERT INTO b_100 values( 2, 2000 );
INSERT INTO b_100 values( 2, 3000 );

commit;

---

现在，请考虑加入此链接:

---

Now please consider this join:

SELECT *
FROM A_100 a
JOIN B_100 b
ON a.x_system = b.x_system AND a.x_system = 1
;

| X_SYSTEM | VAL | X_SYSTEM |  VAL |
|----------|-----|----------|------|
|        1 | 100 |        1 | 1100 |

上面的查询从表B_100中给出了一个唯一的记录.如果在merge语句中使用此连接条件，则合并将运行而不会出现任何错误:

the above query gives one unique record from the table B_100. If you use this join condition in a merge statement, the merge will run without any error:

MERGE INTO A_100 a
USING B_100 b
ON ( a.x_system = b.x_system AND a.x_system = 1)
WHEN MATCHED THEN UPDATE SET a.val = b.val
;

1 row merged. 

---

现在请考虑以下联接:

---

Now please consider the below join:

SELECT *
FROM A_100 a
JOIN B_100 b
ON a.x_system = b.x_system AND a.x_system = 2
;

| X_SYSTEM | VAL | X_SYSTEM |  VAL |
|----------|-----|----------|------|
|        2 | 200 |        2 | 2000 |
|        2 | 200 |        2 | 3000 | 

上面的联接，对于A_100中的一条记录给出了B_100中的两条记录.

如果您尝试在上述连接条件下使用MERGE，则会得到以下信息:

The above join, for one record from A_100 gives two records from B_100.

If you try to use MERGE with the above join condition you will get the following:

MERGE INTO A_100 a
USING B_100 b
ON ( a.x_system = b.x_system AND a.x_system = 2)
WHEN MATCHED THEN UPDATE SET a.val = b.val
;

Error report -
ORA-30926: unable to get a stable set of rows in the source tables

---

Oracle只是说你:

---

Oracle simply says you :

查询左表中的一条记录返回两个值: 右表中的2000和3000.
我无法将右表中的两个值分配给左表中的单个标量字段，这是不可能的.
请更改连接条件，使其仅提供一个唯一的 右表中的记录是左表中的每个记录

The query for one record from the left table returned two values: 2000 and 3000 from the right table.
I can not assign two values from the right table to a single scalar field of the left table, this is impossible.
Please change the join condition so that it gives only one unique record from the right table for each record in the left table

这篇关于合并更新oracle无法获得稳定的行集的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！