Re: Corruption in update statement with dml error logging 10gR2/Solaris
From: Vladimir M. Zakharychev <vladimir.zakharychev_at_gmail.com>
Date: Sun, 24 May 2009 12:00:06 -0700 (PDT)
Message-ID: <13967717-0152-43bf-a6d4-c112508912d5_at_h28g2000yqd.googlegroups.com>
On May 24, 9:23 am, "Ender Wiggin" <ender_wiggi..._at_yahoo.com> wrote:
> update a_table set xyz = ( SELECT distinct
> FROM ... )
> log errors into err_xyz reject limit unlimited ;
>
> There are rows where it can possibly select upwards of 1000 rows and
> distinct will return about 5 different rows. When I update only a set of
> rows, I see that nothing gets updated to oracle error 1427, when I run the
> update statement for the whole table, I get some of the rows updated and
> others not updated. In other words, if I update a set of rows, it does not
> update any data for that set of rows. When I run this statement for the
> whole table, rows get updated for the set I just tested. Did anyone see this
> behaviour ?
Date: Sun, 24 May 2009 12:00:06 -0700 (PDT)
Message-ID: <13967717-0152-43bf-a6d4-c112508912d5_at_h28g2000yqd.googlegroups.com>
On May 24, 9:23 am, "Ender Wiggin" <ender_wiggi..._at_yahoo.com> wrote:
> update a_table set xyz = ( SELECT distinct
> FROM ... )
> log errors into err_xyz reject limit unlimited ;
>
> There are rows where it can possibly select upwards of 1000 rows and
> distinct will return about 5 different rows. When I update only a set of
> rows, I see that nothing gets updated to oracle error 1427, when I run the
> update statement for the whole table, I get some of the rows updated and
> others not updated. In other words, if I update a set of rows, it does not
> update any data for that set of rows. When I run this statement for the
> whole table, rows get updated for the set I just tested. Did anyone see this
> behaviour ?
From what you posted I figured that your inner query (SELECT distinct FROM ...) may return multiple rows in some cases. Update expects that for every row being updated the inner query returns exactly one row with new value(s) to be set, otherwise ORA-01427 is reported. To better understand why this happens in your case and how it can be avoided, more details are needed. Can you give an example (the data and the statement) so that we could better understand your issue and suggest appropriate fix?
Regards,
Vladimir M. Zakharychev
N-Networks, makers of Dynamic PSP(tm)
http://www.dynamicpsp.com
Received on Sun May 24 2009 - 14:00:06 CDT