Weird problem: Update statement updating records it should not

From: <zzzzzz45_at_hotmail.com>
Date: Tue, 10 Jul 2007 04:03:23 -0700
Message-ID: <1184065403.087436.212820@57g2000hsv.googlegroups.com>

We recently upgraded to 10g. An update statement that previously worked fine is giving an error. Having looked at what it's doing all I can say is it's very weird

The syntax of the update is:

update table1                -- THIS UPDATES 54 RECORDS

set (field1, field2, field3) =
(

   select field4, field5, 1
   from table2, table3
   where table2.field4 = table3.field5
   and table1.field1 = table2.field4
)
where table1.field1 in
(

    select field4 -- THIS RETURNS 45 record keys     from table2, table3
    where table2.field4 = table3.field5
);

Field3 won't accept nulls and from the above SQL should never be set to null. But the error is that field3 can't be set to null. The SQL is attempting to update records in table1 whose key value (field1) is not coming from the second select. I can't understand this.

I know the IDs of the fields that are being updated in error. If I try the second select on its own and include

and table2.field4 in (val1, val2, val3)

I don't get any records!

Yet if I add:

and not table2.field4 in (val1, val2, val3)

to the second select and run the update the correct 45 records get updated and the records with keys of val1, val2, val3 don't get updated.

I'm perplexed by this...

Mark Received on Tue Jul 10 2007 - 06:03:23 CDT