Copyright (c) 1982, 2010, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
create table table1 (code int,Org int,Descr varchar(20));
Table created.
create table table2 (per_code int,Name varchar(20));
Table created.
insert into table1 values (1,100,'Rec1');
1 row created.
insert into table1 values (2,200,'Rec2');
1 row created.
insert into table1 values (3,300,'Rec3');
1 row created.
insert into table2 values (1,'TestRec1');
1 row created.
insert into table2 values (2,'TestRec2');
1 row created.
update table1 set Org=0 where code in (select code from table2);
3 rows updated.
select * from table1;
CODE ORG DESCRTable created.
create table table2 (per_code int,Name varchar(20));
Table created.
insert into table1 values (1,100,'Rec1');
1 row created.
insert into table1 values (2,200,'Rec2');
1 row created.
insert into table1 values (3,300,'Rec3');
1 row created.
insert into table2 values (1,'TestRec1');
1 row created.
insert into table2 values (2,'TestRec2');
1 row created.
update table1 set Org=0 where code in (select code from table2);
3 rows updated.
select * from table1;
---------- ---------- --------------------
1 0 Rec1
2 0 Rec2
3 0 Rec3
>
Not able to duplicate.
Can you show spool output and version or database?
From: oracle-l-bounce_at_freelists.org [oracle-l-bounce_at_freelists.org] On Behalf Of Jack van Zanen [jack_at_vanzanen.com]
Sent: Tuesday, November 02, 2010 6:10 PM
To: oracle-l@freelists.org
Subject: 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
