THIS ERROR IS BECAUSE, TABLE DON'T HAVE PRIMARY KEY
-----Original Message-----
From: Dan Pappas
[mailto:oracledba-ezmlmshield-x66255212.[Email address protected]
Sent: Wednesday, September 07, 2005 1:56 AM
To: LazyDBA Discussion
Subject: RE: MERGE statement - ORA-30926: unable to get a stable set of
rows in the source tables
Unfortunately no. I could have multiple records that are all valid on
the same day. I would like to do the insert/update process based
chronologically if possible. However, if that is not possible, using
rownum would be acceptable.
________________________________
From: Keith Williams [mailto:oracledba-ezmlmshield-x43365093.[Email
address protected]
Sent: Tue 9/6/2005 2:24 PM
To: LazyDBA Discussion
Subject: RE: MERGE statement - ORA-30926: unable to get a stable set of
rows in the source tables
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.
--------
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
--------
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
_ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _
Confidentiality Notice
The information contained in this electronic message and any attachments to this message are intended
for the exclusive use of the addressee(s) and may contain confidential or privileged information. If
you are not the intended recipient, please notify the sender at Hinduja TMT Ltd or [Email address protected] immediately
and destroy all copies of this message and any attachments.
Oracle LazyDBA home page