Hello All:

I am trying to use the merge command where the source table has multiple input records with the same primary key. I am using Oracle9i. Does anyone know a simple solution to get around the ORA-30926 error?

Thanks in advance.

Dan

SQL> MERGE INTO T_CUSTOMER A
2 USING (SELECT
3 ACCT_NUM,
4 TEXT_1,
5 TEXT_2,
6 LAST_UPDATE_DT
7 FROM WS_CUSTOMER) B
8 ON (A.ACCT_NUM = B.ACCT_NUM)
9 WHEN MATCHED THEN
10 UPDATE SET
11 A.TEXT_1 = B.TEXT_1,
12 A.TEXT_2 = B.TEXT_2,
13 A.LAST_UPDATE_DT = B.LAST_UPDATE_DT
14 WHEN NOT MATCHED THEN
15 INSERT (
16 A.ACCT_NUM,
17 A.TEXT_1,
18 A.TEXT_2,
19 A.LAST_UPDATE_DT
20 )
21 VALUES (
22 B.ACCT_NUM,
23 B.TEXT_1,
24 B.TEXT_2,
25 B.LAST_UPDATE_DT
26 )
27 ;
ON (A.ACCT_NUM = B.ACCT_NUM)
*
ERROR at line 8:
ORA-30926: unable to get a stable set of rows in the source tables




create table WS_CUSTOMER (
ACCT_NUM VARCHAR2(18) null,
TEXT_1 VARCHAR2(10) null,
TEXT_2 VARCHAR2(10) null,
LAST_UPDATE_DT DATE DEFAULT SYSDATE)
INITRANS 1 MAXTRANS 50 TABLESPACE WS_CUSTOMER_S PCTFREE 10 PCTUSED 20
LOGGING
PARALLEL
NOCACHE
;
SHOW SQLCODE
COMMIT;
create table T_CUSTOMER (
ACCT_NUM VARCHAR2(18) not null,
TEXT_1 VARCHAR2(10) null,
TEXT_2 VARCHAR2(10) null,
LAST_UPDATE_DT DATE DEFAULT SYSDATE,
constraint PK_CUSTOMER
primary key (ACCT_NUM)
USING INDEX TABLESPACE E2E_CUSTOMER_INDEX_S
)
INITRANS 1 MAXTRANS 50 TABLESPACE E2E_CUSTOMER_S PCTFREE 10 PCTUSED 20
LOGGING
PARALLEL
NOCACHE
;
SHOW SQLCODE
COMMIT;

Oracle LazyDBA home page