RE: Update not behaving as hoped
- From: Michael Dinh <mdinh@xxxxxxxxx>
- To: "jack@xxxxxxxxxxxx" <jack@xxxxxxxxxxxx>, Mindaugas Navickas <mnavickas@xxxxxxxxx>
- Date: Tue, 2 Nov 2010 20:18:38 -0700
DUH!
I remember we had something like this. Oracle actually rewrites the SQL.
Since code is a valid column in the outer table, Oracle made the assumptions.
If you put in column that does not exist in either tables, then you should get
error.
IF you alias the table, then it would fail as well, as mentioned in thread
earlier.
> update table1 set Org=0 where code in (select code from table2);
3 rows updated.
Execution Plan
----------------------------------------------------------
Plan hash value: 1937641525
-------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------
| 0 | UPDATE STATEMENT | | 1 | 26 | 334 (0)| 00:41:06 |
| 1 | UPDATE | TABLE1 | | | | |
|* 2 | FILTER | | | | | |
| 3 | TABLE ACCESS FULL | TABLE1 | 3 | 78 | 167 (0)| 00:20:33 |
|* 4 | FILTER | | | | | |
| 5 | TABLE ACCESS FULL| TABLE2 | 2 | | 167 (0)| 00:20:33 |
-------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter( EXISTS (SELECT 0 FROM "TABLE2" "TABLE2" WHERE :B1=:B2))
4 - filter(:B1=:B2)
Note
-----
- dynamic sampling used for this statement (level=2)
Statistics
----------------------------------------------------------
9 recursive calls
3 db block gets
119 consistent gets
0 physical reads
872 redo size
838 bytes sent via SQL*Net to client
814 bytes received via SQL*Net from client
3 SQL*Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
3 rows processed
________________________________________
From: oracle-l-bounce@xxxxxxxxxxxxx [oracle-l-bounce@xxxxxxxxxxxxx] On Behalf
Of Jack van Zanen [jack@xxxxxxxxxxxx]
Sent: Tuesday, November 02, 2010 8:06 PM
To: Mindaugas Navickas
Cc: oracle-l@xxxxxxxxxxxxx
Subject: Re: Update not behaving as hoped
This query is not a correlated subquery, nor is it intended to be.
I just wish to update table1 for which the code exists in the per_code field in
table2. As per below query which does exactly that.
update
table1
set Org=0
where code in (select per_code from table2);
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:50 PM, Mindaugas Navickas
<mnavickas@xxxxxxxxx<mailto:mnavickas@xxxxxxxxx>> wrote:
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@xxxxxxxxxxxx<mailto:jack@xxxxxxxxxxxx>>
To: Mindaugas Navickas <mnavickas@xxxxxxxxx<mailto:mnavickas@xxxxxxxxx>>
Cc: oracle-l@xxxxxxxxxxxxx<mailto:oracle-l@xxxxxxxxxxxxx>
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@xxxxxxxxx<mailto:mnavickas@xxxxxxxxx>> 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
set Org=0
where exists (select 1 from table2 where code=per_code);
As well as this (which would be equivelent to one that you have send)
update table1
set Org=0
where exists (select 1 from table2 where code=code);
Regards
Mike Navickas
Oracle&DB2 DBA
________________________________
From: Jack van Zanen <jack@xxxxxxxxxxxx<mailto:jack@xxxxxxxxxxxx>>
To: oracle-l@xxxxxxxxxxxxx<mailto:oracle-l@xxxxxxxxxxxxx>
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:
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:
