Update not behaving as hoped
- From: Jack van Zanen <jack@xxxxxxxxxxxx>
- To: oracle-l@xxxxxxxxxxxxx
- Date: Wed, 3 Nov 2010 12:10:40 +1100
Hi All,
Oracle 11 / Sql Server (same behaviour)
My wife encountered following issue on sql server and asked me to check if
oracle was "smarter". It seems to not be.
Test case:
create table table1
(code int,Org int,Descr varchar(20));
create table table2
(per_code int,Name varchar(20));
insert into table1 values (1,100,'Rec1');
insert into table1 values (2,200,'Rec2');
insert into table1 values (3,300,'Rec3');
insert into table2 values (1,'TestRec1');
insert into table2 values (2,'TestRec2');
update table1
set Org=0
where code in (select code from table2); --There is a syntax error "invalid
identifier" in the subselect but there is no error when running this update.
select * from table1; --Not only was there no error but it updated the
entire table.
This is probably "normal" behaviour since both oracle and sql server do it
but I would have hoped that a syntax error would result in an error no
matter where it happens. Can anybody explain the technical reason why this
would be happening.
Jack
Other related posts:
