• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 442
  • Last Modified:

ORA-30926: UNABLE TO GET A STABLE SET OF ROWS IN THE SOURCE TABLES

hi am having this error when meging two tables
am in 11gR2
error is ORA-30926: UNABLE TO GET A STABLE SET OF ROWS IN THE SOURCE TABLES

create table CAL_OBJ
(
  obj_name    VARCHAR2(100) not null,
  obj_type    VARCHAR2(50),
  obj_title   VARCHAR2(50),
  description VARCHAR2(500),
  sub_system  VARCHAR2(100),
  status      VARCHAR2(20)
);


create table OBJTEST
(
  obj_name    VARCHAR2(100),
  obj_type    VARCHAR2(50),
  obj_title   VARCHAR2(50),
  description VARCHAR2(500),
  sub_system  VARCHAR2(100),
  status      VARCHAR2(20)
);

merge into cal_obj e1
      using objtest e2
      on (e1.obj_name = e2.obj_name)
when matched then update set e1.description    = e2.description,
                             e1.sub_system     = e2.sub_system
        when not matched then insert
         values (e2.obj_name, e2.obj_type, null, e2.description,e2.sub_system,null);

Open in new window

0
chalie001
Asked:
chalie001
1 Solution
 
chaauCommented:
Your on clause  on (e1.obj_name = e2.obj_name) does not provide the uniqueness. There are records in either tables with the duplicate obj_name. You need to fix your data by removing the duplicates or by using a stricter condition in the ON clause. BTW, I can see that the tables are without any primary keys or indexes. It does not look right. You need to start thinking about re-designing it.
0
 
chalie001Author Commented:
correct answer
0

Featured Post

NFR key for Veeam Agent for Linux

Veeam is happy to provide a free NFR license for one year.  It allows for the non‑production use and valid for five workstations and two servers. Veeam Agent for Linux is a simple backup tool for your Linux installations, both on‑premises and in the public cloud.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now