Re: Update not behaving as hoped
- From: "Lei Liu (sundog315)" <sundog315@xxxxxxxxx>
- To: "jack" <jack@xxxxxxxxxxxx>, "oracle-l" <oracle-l@xxxxxxxxxxxxx>
- Date: Wed, 3 Nov 2010 10:40:32 +0800
HI,
you should use alias. otherwise it update all record in table1;
update table1 t
set t.Org=0
where t.code in (select x.code from table2 x); --it will raise syntax error
------------------
Lei Liu (sundog315)
2010-11-03
-------------------------------------------------------------
发件人：Jack van Zanen
发送日期：2010-11-03 09:12:16
收件人：oracle-l
抄送：
主题：Update not behaving as hoped
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
��i��0���zX���+�� n��{�+i�^
Other related posts:
