Re: Corruption in update statement with dml error logging 10gR2/Solaris

From: Mark D Powell <>
Date: Mon, 25 May 2009 07:44:24 -0700 (PDT)
Message-ID: <>

On May 25, 12:06 am, "Ender Wiggin" <> wrote:
> 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" <>, iletide sunu
> > On May 24, 9:23 am, "Ender Wiggin" <> 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)
> > Hide quoted text -
> - Show quoted text -

Ender, Often whey your update set clasue has a subquery that provides the single expected value to be assigned the same set of sub-query conditions need to be applied to the outer query where clause. It seems from your problem description that you omitted the where clause restricton. Also from your post of only part of the subquery I cannot tell if you remembered to coordinate it back to the driving table (update target).

General form:
 update target t
  set col1 = ( select value from table_b b where b,key = t.key and conditions ....)
where exists ( select x from table_b c where c.key = t.key and conditions ...)

If the subquery can return more than one row the rownum = 1 can be used to limit the return to one value where the column in question can be expected to have the same value in all rows in the sub-set. Exists and not exists clauses can be used where the result is based on a value or no value being found. And the CASE, count(*), and/or nullif functions are sometimes of use where choices have to made in the value returned.

HTH -- Mark D Powell -- Received on Mon May 25 2009 - 09:44:24 CDT

Original text of this message