ORA-30926: 无法在源表中获得一组稳定的行

2021-12-05 00:00:00 oracle sql-merge

我得到了

ORA-30926: 无法在源表中获得一组稳定的行

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

在以下查询中:

  MERGE INTO table_1 a
      USING 
      (SELECT a.ROWID row_id, 'Y'
              FROM table_1 a ,table_2 b ,table_3 c
              WHERE a.mbr = c.mbr
              AND b.head = c.head
              AND b.type_of_action <> '6') src
              ON ( a.ROWID = src.row_id )
  WHEN MATCHED THEN UPDATE SET in_correct = 'Y';

我运行了 table_1 它有数据，我也运行了内部查询 (src)，它也有数据.

I've ran table_1 it has data and also I've ran the inside query (src) which also has data.

为什么会出现这个错误，如何解决?

Why would this error come and how can it be resolved?

推荐答案

这通常是由 USING 子句中指定的查询中的重复项引起的.这可能意味着 TABLE_A 是父表，并且多次返回相同的 ROWID.

This is usually caused by duplicates in the query specified in USING clause. This probably means that TABLE_A is a parent table and the same ROWID is returned several times.

您可以通过在查询中使用 DISTINCT 来快速解决问题(实际上，如果Y"是一个常量值，您甚至不需要将其放入查询中).

You could quickly solve the problem by using a DISTINCT in your query (in fact, if 'Y' is a constant value you don't even need to put it in the query).

假设您的查询是正确的(不知道您的表)，您可以执行以下操作:

Assuming your query is correct (don't know your tables) you could do something like this:

  MERGE INTO table_1 a
      USING 
      (SELECT distinct ta.ROWID row_id
              FROM table_1 a ,table_2 b ,table_3 c
              WHERE a.mbr = c.mbr
              AND b.head = c.head
              AND b.type_of_action <> '6') src
              ON ( a.ROWID = src.row_id )
  WHEN MATCHED THEN UPDATE SET in_correct = 'Y';

相关文章