Re: Corruption in update statement with dml error logging 10gR2/Solaris
Date: Sun, 24 May 2009 22:06:37 -0600
Message-ID: <gvd5h4$gs0$1_at_aioe.org>
You are right. I was expecting to see multiple rows but I was not too worried. Basically logic is I have a variable that I update for all values of null, whatever does not update I update with a second more specific update. What I am scared of is that for this set of rows "pertaining to an individual", none should get updated because it will return multiple rows from the select, however I am seeing it being updated as if by data from the other individuals in this table.
Every row for this set ends up in the error table with 1427 if I only update this individual's data. Exactly what I expect. If I then run the update statement for the whole table, I end up with some of the rows from this set in the error table.
In short,
update abc_table set city = ( select distinct city from address,...... )
where city is null -- will not update any of his rows if city is null for
this individual's set of records
When you run the same statement for all individuals in the table "city is
null for all individuals", the city gets updated for some of the rows for
this individual. Hence I have data inconsistency.... which is scary...
"Vladimir M. Zakharychev" <vladimir.zakharychev_at_gmail.com>, iletide sunu
yazdi
news: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 - 23:06:37 CDT