Merge Error Ora-30926
Contents |
here for a quick overview of the site Help Center Detailed answers to any questions you might have Meta Discuss the workings and policies of this site About ora-30926 error in oracle Us Learn more about Stack Overflow the company Business Learn more about hiring
Ora-30926 Ask Tom
developers or posting ads with us Stack Overflow Questions Jobs Documentation Tags Users Badges Ask Question x Dismiss Join the how to resolve ora-30926 Stack Overflow Community Stack Overflow is a community of 6.2 million programmers, just like you, helping each other. Join them; it only takes a minute: Sign up ORA-30926: unable to get a stable remove any non-deterministic where clauses and reissue the dml. set of rows in the source tables up vote 78 down vote favorite 13 I am getting ORA-30926: unable to get a stable set of rows in the source tables in the following query: 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
Ora-30926 Techonthenet
= src.row_id ) WHEN MATCHED THEN UPDATE SET in_correct = 'Y'; 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? oracle sql-merge share|improve this question edited Mar 10 at 9:45 diziaq 2,07181532 asked Feb 25 '10 at 20:13 Omnipresent 10.6k33108161 add a comment| 4 Answers 4 active oldest votes up vote 117 down vote accepted 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. 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'; share|improve this answer
CommunityOracle User Group CommunityTopliners CommunityOTN Speaker BureauJava CommunityError: You don't have JavaScript enabled. This tool uses JavaScript and much of it will not work ora-39126: worker unexpected fatal error in kupw$worker.stats_load [marker] correctly without it enabled. Please turn JavaScript back on non-deterministic where clauses oracle and reload this page. Please enter a title. You can not post a blank
Ora-30926 Merge Update
message. Please type your message and try again. More discussions in PL/SQL and SQL All PlacesDatabaseDatabase Application DevelopmentPL/SQL and SQL This discussion is http://stackoverflow.com/questions/2337271/ora-30926-unable-to-get-a-stable-set-of-rows-in-the-source-tables archived 13 Replies Latest reply on Feb 23, 2013 4:18 PM by Peter Gjelstrup MERGE STATEMENT ORA-30926: unable to get a stable set of rows in the source 959406 Feb 21, 2013 1:57 PM hi all transactional table issue both equal condition(ie on clause common row https://community.oracle.com/thread/2503509 not any) not avilable what can i do_+ in this merge statement CORM_IRT_INV_RESOURCE_Tmp and PEG6_WRDT_RES_DTS_TMP (both temp/transaction table) . error are display oracle - ORA-30926: unable to get a stable set of rows in the source i ask both table common any thing AVAILABLE USING on clause ANY SOLUTION PLZ PROVIDE ME MERGE INTO CORM_IRT_INV_RESOURCE_Tmp A USING (SELECT DISTINCT * FROM PEG6_WRDT_RES_DTS_TMP) B ON ( B.WRDT_GUID = A.IRT_GUID AND B.WRDT_DOCUMENT_NO = A.IRT_ORDER_NO AND B.WRDT_DOCUMENT_OU = A.IRT_ORDER_OU AND B.WRDT_REF_LINE_NO = A.IRT_REF_LINE_NO AND B.WRDT_TASK_LINE_NO = A.IRT_LINE_NO AND B.WRDT_RESOURCE_NO = A.IRT_RESOURCE_CODE AND B.WRDT_RES_TYPE = A.IRT_RESOUCE_TYPE AND A.IRT_GUID = v_Guid_Tmp AND A.IRT_ORDER_NO = v_cusorderno_Tmp AND A.IRT_ORDER_OU = v_COrderOU_Tmp) WHEN MATCHED THEN UPDATE SET A.IRT_NORMALRATE_PERHR = B.WRDT_RATE_PER_HR, A.IRT_OTRATE_PERHR = B.WRDT_OVERTIME_RATE_PER_HR, A.irt_facilityrate_perhr = b.wrdt_std_fac_rate, A.irt_resource_price = --sqlserver_utilities.round_(NVL(IRT_BILLABLE_HR_NO, 0) * NVL(B.WRDT_RATE_PER_HR, 0), v_pamt_tmp) + sqlserver_utilities.round_(NVL(IRT_BILLABLE_HR_OT, 0) * NVL(B.WRDT_OVERTIME_RATE_PER_HR, 0), v_pamt_tmp), round(NVL(IRT_BILLABLE_HR_NO, 0) * NV
set of rows in the sourcetables Posted on January 29, 2013 by Aykut Akin In my recent project I get this error while I'm using Merge in Oracle. After some searching I found the problem and decided to write a post about it:) The reason https://aykutakin.wordpress.com/2013/01/29/ora-30926-unable-to-get-a-stable-set-of-rows-in-the-source-tables/ of this error is source table. In merge queries we have source table and target table. We make some operations (Update, Insert, Delete) according to source table. We connect target table and source table in ON clause of Merge. And merge expects that source table returns unique values according to ON clause. If your columns, that you use in the ON clause, don't provide unique key feature, you will get this error too.. Don't forget primary key error in is a unique key;) I think this is reasonable expectation. Think about it: what happens if source table returns non-unique values? Which data should be used to update rows? How can Oracle select this? So this is why Oracle expect uniqueness in On clause. Let me make you see this error with example: CREATE TABLE source_table ( col1 NUMBER, col2 VARCHAR2(10), col3 VARCHAR2(10) ); INSERT INTO source_table (col1, col2, col3) VALUES (1, 'a', 'w'); INSERT INTO source_table (col1, non-deterministic where clauses col2, col3) VALUES (1, 'b', 'x'); INSERT INTO source_table (col1, col2, col3) VALUES (2, 'c', 'y'); INSERT INTO source_table (col1, col2, col3) VALUES (3, 'c', 'z'); COMMIT; CREATE TABLE target_table ( col1 NUMBER, col2 VARCHAR2(10), col3 VARCHAR2(10) ); INSERT INTO target_table (col1, col2, col3) VALUES (1, 'b', 'z'); INSERT INTO target_table (col1, col2, col3) VALUES (3, 'd', 'w'); COMMIT; In above queries we just simply create sample data. Now we are going to merge two table. MERGE INTO target_table trg USING (--Actually we can simply write source_table for this example but I want to write Select:) SELECT col1, col2, col3 FROM source_table ) src ON (trg.col1 = src.col1) WHEN MATCHED THEN UPDATE SET --Don't forget you cannot update columns that included in ON clause trg.col2 = src.col2, trg.col3 = src.col3 WHEN NOT MATCHED THEN INSERT ( col1, col2, col3 ) VALUES ( src.col1, src.col2, src.col3 ); COMMIT; Aaand boom! We get the error! We used col1 column for merge operation. And in our source table col1 is not unique. We have two different rows in source_table that col1 column is '1'. So our merge query couldn't decide the which one has desirable value. (1,'a','w') OR (1,'b',x')? You couldn't decide it too, didn't you:) Now let's make correct this merge query. I'm looking rows and as I see col1 AND col2 provide unique values together. If we redesign our query and ma