Re: Update not behaving as hoped

From: Jack van Zanen <jack_at_vanzanen.com>
Date: Wed, 3 Nov 2010 13:36:59 +1100
Message-ID: <AANLkTinrc2c6RhADs1aL=abfj01pv5_-b9oVDWA0J8hc_at_mail.gmail.com>

That is exactly what I see, however the subselect on it's own is invalid sql. As a result it updated the entire table -- not what I expected.

Jack van Zanen

---

This e-mail and any attachments may contain confidential material for the sole use of the intended recipient. If you are not the intended recipient, please be aware that any disclosure, copying, distribution or use of this e-mail or any attachment is prohibited. If you have received this e-mail in error, please contact the sender and delete all copies. Thank you for your cooperation

On Wed, Nov 3, 2010 at 1:12 PM, Michael Dinh <mdinh_at_xifin.com> wrote:

> 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:36:59 CDT