RE: Update not behaving as hoped

From: Michael Dinh <mdinh_at_XIFIN.Com>
Date: Tue, 2 Nov 2010 19:12:05 -0700
Message-ID: <D29F9902E534D5478F2E83FD6A44B3068CF2CA1C_at_mail02.mba.xifin.com>

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_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_at_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

--
http://www.freelists.org/webpage/oracle-l

Received on Tue Nov 02 2010 - 21:12:05 CDT