Re: Update not behaving as hoped

From: Lei Liu (sundog315) <"Lei>
Date: Wed, 3 Nov 2010 10:40:32 +0800
Message-ID: <201011031040270799180_at_gmail.com>

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�^ Received on Tue Nov 02 2010 - 21:40:32 CDT