Re: Weird problem: Update statement updating records it should not

On Jul 10, 6:03 am, zzzzz..._at_hotmail.com wrote:
> 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

Post the EXACT error the ACTUAL update statement is producing, as well as the actual update statement you're using along with the DDL for all involved tables. Your 'condensed version' is missing vital information and we won't have the complete picture until you provide all of the missing pieces.

David Fitzjarrell Received on Tue Jul 10 2007 - 07:57:20 CDT