Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
![]() |
![]() |
Home -> Community -> Usenet -> c.d.o.misc -> Weird problem: Update statement updating records it should not
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 RECORDSset (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
![]() |
![]() |