You have to write you select statement to eliminate all but one of the
source rows. I would assume from your example that you want the latest
LAST_UPDATE_DT for each ACCT_NUM.
-----Original Message-----
From: Dan Pappas
[mailto:oracledba-ezmlmshield-x34610579.[Email address protected]
Sent: Tuesday, September 06, 2005 12:47 PM
To: LazyDBA Discussion
Subject: MERGE statement - ORA-30926: unable to get a stable set of rows
in the source tables
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;
--------
website: http://www.LazyDBA.com
Please don't reply to RTFM questions
Oracle documentation is here: http://tahiti.oracle.com
To unsubscribe: see http://www.lazydba.com/unsubscribe.html
To subscribe: see http://www.lazydba.com
By using this list you agree to these
terms:http://www.lazydba.com/legal.html
IMPORTANT NOTICE:
This message may contain confidential information. If you have received this e-mail in error, do not use, copy or distribute it. Do not open any attachments. Delete it immediately from your system and notify the sender promptly by e-mail that you have done so. Thank you.
Oracle LazyDBA home page