Re: Update not behaving as hoped

From: Wolfgang Breitling <breitliw_at_centrexcc.com>
Date: Tue, 02 Nov 2010 21:55:21 -0600
Message-Id: <201011030355.oA33tIfP006959_at_mail139c0.megamailservers.com>

Since you did not qualify "code" in the subselect

update
table1
set Org=0
where code in (select code from table2);

and code does not exist in table2 it refers back to field code in table1. Thus your where clause becomes true for every row. The query is syntactically correct and executes correctly. It is your "translation" of your intend into sql that is in error. This mistake underscores the importance to qualify all columns used in a sql. As a principle, not only to avoid ambiguities.

This sql

update table1 t1
set Org=0
where t1.code in (select t2.code from table2 t2);

DOES raise the error you expect.

At 09:06 PM 11/2/2010, you wrote:
>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);
>

Regards

Wolfgang Breitling
Centrex Consulting Corporation
http://www.centrexcc.com

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

Received on Tue Nov 02 2010 - 22:55:21 CDT