Re: Update not behaving as hoped

Jack,

Please google on "correlated subquery" and I am sure that you will find answers 
that you are looking for. 

Regards
Mike Navickas
Oracle&DB2 DBA



________________________________
From: Jack van Zanen <jack_at_vanzanen.com>
To: Mindaugas Navickas <mnavickas_at_yahoo.com>
Cc: oracle-l_at_freelists.org
Sent: Tue, November 2, 2010 10:35:06 PM
Subject: Re: Update not behaving as hoped


try running the subselect on it's own

table2 has NO column named code.


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 12:49 PM, Mindaugas Navickas <mnavickas_at_yahoo.com> wrote:

Jack,
>
>I might not be as "smart" as Oracle and SQL servers together, but I can not see 
>that syntax error as you see it. To it looks "normal" that sub-select has some 
>columns from outer statement - for example - would you consider this update 
>"normal":
>
>update table1 
>setOrg=0
>whereexists(select 1 fromtable2 where code=per_code);
> 
>As well as this (which would be equivelent to one that you have send)
>
>update table1 
>setOrg=0
>where exists(select 1 fromtable2 where code=code);
>
>
>Regards
>Mike Navickas
>Oracle&DB2 DBA
>
> 
>
>
>
________________________________
From: Jack van Zanen <jack_at_vanzanen.com>
>To: oracle-l_at_freelists.org
>Sent: Tue, November 2, 2010 9:10:40 PM
>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:
>
>createtabletable1
>(code int,Org int,Descr varchar(20));createtabletable2
>(per_code int,Namevarchar(20));insertintotable1 values 
>(1,100,'Rec1');insertintotable1 values (2,200,'Rec2');insertintotable1 values 
>(3,300,'Rec3');insertintotable2 values(1,'TestRec1');insertintotable2 
>values(2,'TestRec2');updatetable1 
>
>setOrg=0
>wherecode in(selectcode fromtable2);  --There is a syntax error "invalid 
>identifier" in the subselect but there is no error when running this 
>update.select*fromtable1; --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