Home » SQL & PL/SQL » SQL & PL/SQL » Merge,Insert -ORA-30926
icon9.gif  Merge,Insert -ORA-30926 [message #143834] Sun, 23 October 2005 03:49 Go to next message
mymot
Messages: 225
Registered: July 2005
Senior Member
merge into p2
using(
select aname,max_time,did,v1,v2
from (select x.* from (select aname,max(timestamp) over (partition by timestamp,aname,did order by timestamp) max_time,did,
sum(id1) v1,
sum(id2) v2
from p1
group by aname, timestamp,did
) X
)) y on (p2.aname = y.aname and p2.did = y.did)
when matched then update set id1 = y.v1,id2=y.v2
when not matched then
insert (aname,timestamp,did,id1,id2) values(y.aname,y.max_time,y.did,y.v1,y.v2);

SQL> select * from p1;

JUR ANAME TIMESTAMP DID ID1 ID2
---------- ---------- -------------------- ---------- ---------- ----------
P1 M1 22-oct-2005 17:36:44 11 1 1
P2 M1 22-oct-2005 17:36:44 11 1 1
P2 M2 22-oct-2005 17:36:44 12 2 2
P2 M2 22-oct-2005 17:36:44 12 1 2
P2 M2 22-oct-2005 17:46:44 12 1 1
P3 M2 22-oct-2005 17:46:44 12 11 11
F1 M1 22-oct-2005 17:56:44 11 1 1
F2 M1 22-oct-2005 17:56:44 11 1 2
F3 M2 22-oct-2005 17:56:44 12 1 2
F2 M1 22-oct-2005 18:05:44 11 1.3 99.3

10 rows selected.

SQL> select * from p2;

ANAME TIMESTAMP DID ID1 ID2
---------- -------------------- ---------- ---------- ----------
M1 22-oct-2005 18:05:44 11 1.3 99.3
M1 22-oct-2005 17:56:44 11 2 3
M1 22-oct-2005 17:36:44 11 2 2
M2 22-oct-2005 17:56:44 12 1 2
M2 22-oct-2005 17:46:44 12 12 12
M2 22-oct-2005 17:36:44 12 3 4

6 rows selected.

SQL> @s11.sql
merge into p2
*
ERROR at line 1:
ORA-30926: unable to get a stable set of rows in the source tables

wHAT'S problem with above query?
thanks
Re: Merge,Insert -ORA-30926 [message #143843 is a reply to message #143834] Sun, 23 October 2005 06:01 Go to previous messageGo to next message
mchadder
Messages: 224
Registered: May 2005
Location: UK
Senior Member
Hello there.

From metalink note : 96858.1

Error: ORA-30926
Text:  Unable to get a stable set of rows in the source tables.
--------------------------------------------------------------------
Cause:  A stable set of rows could not be got because of large dml 
        activity or a non-deterministic where clause. 

Action: Remove any non-deterministic where clauses and reissue the dml.

But, I think, it's due to the fact that you're updating multiple rows on a match, i.e. check this out :
https://metalink.oracle.com/metalink/plsql/ml2_documents.showDocument?p_database_id=NOT&p_id=137202.1

Regards
icon14.gif  Re: Merge,Insert -ORA-30926 [message #143985 is a reply to message #143843] Mon, 24 October 2005 09:33 Go to previous message
mymot
Messages: 225
Registered: July 2005
Senior Member
thanks mchadder.

it works fine.

thanks

merge into p2
using (select a.aname,timestamp,a.did,sum(a.id1) p1_id1,sum(a.id2) p1_id2 from p1 a group by a.aname,a.did,timestamp ) p1
on(p1.aname = p2.aname and p1.did = p2.did and p1.timestamp = p2.timestamp)
when matched then
update set id1 = p1_id1,id2 = p1_id2
when not matched then
insert (aname,timestamp,did,id1,id2) values(p1.aname,p1.timestamp,p1.did,p1.p1_id1,p1.p1_id2);
Previous Topic: Formatting SQL Query output with heading and lines
Next Topic: trigger creation error
Goto Forum:
  


Current Time: Fri Aug 01 07:48:35 CDT 2014

Total time taken to generate the page: 0.09517 seconds