Re: merge and ORA-30926
- From: Ls Cheng <exriscer@xxxxxxxxx>
- To: Dominic Brooks <dombrooks@xxxxxxxxxxx>
- Date: Fri, 26 Sep 2014 09:20:52 +0200
Hi Dominic
In my second example there are two values in t12.y when join with t11 so it
should change the target row twice, no? But the error is not raised.
I added ORDER BY as the example in your blog, it turns out that if I order
by the join key I get ORA-30926 if I order by the other colum it merges ok.
Running 11.2.0.3.
SQL> merge into t11
2 using (select * from t12 order by x) t12
3 on (t11.x = t12.x)
4 when matched then update set y = t12.y;
merge into t11
*
ERROR at line 1:
ORA-30926: unable to get a stable set of rows in the source tables
SQL> merge into t11
2 using (select * from t12 order by y) t12
3 on (t11.x = t12.x)
4 when matched then update set y = t12.y;
2 rows merged.
Thanks
On Fri, Sep 26, 2014 at 8:57 AM, Dominic Brooks <dombrooks@xxxxxxxxxxx>
wrote:
> From previous observations, I believe the error is raised if both source
> rows would actually change the value of the same target row. It's a little
> bit unsatisfactory and inconsistent (e.g. Change behaviour with an order by)
>
> http://orastory.wordpress.com/2011/10/13/merge-oddity/
>
>
> >
>
Other related posts:
