RE: Update not behaving as hoped
- From: Michael Dinh <mdinh@xxxxxxxxx>
- To: "jack@xxxxxxxxxxxx" <jack@xxxxxxxxxxxx>, "oracle-l@xxxxxxxxxxxxx" <oracle-l@xxxxxxxxxxxxx>
- Date: Tue, 2 Nov 2010 19:12:05 -0700
SQL*Plus: Release 11.2.0.2.0 Production on Tue Nov 2 19:09:25 2010
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 DESCR
---------- ---------- --------------------
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@xxxxxxxxxxxxx [oracle-l-bounce@xxxxxxxxxxxxx] On Behalf
Of Jack van Zanen [jack@xxxxxxxxxxxx]
Sent: Tuesday, November 02, 2010 6:10 PM
To: oracle-l@xxxxxxxxxxxxx
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
--
//www.freelists.org/webpage/oracle-l
Other related posts:
