RE: Are these suppose to be equivalent?
- From: "Powell, Mark D" <mark.powell@xxxxxxx>
- To: oracle-l@xxxxxxxxxxxxx
- Date: Tue, 28 Sep 2004 12:54:11 -0400
The first update is missing the where clause that should exist to limit the
rows affected by the update to only those rows that have a corresponding row
in the update subquery. Rows in the target table without a match will have
the target column nullified. This is a common newbie error. Even if a 1:1
relationship is specified in the application specification unless there is a
constraining mechanism in place in the database the SQL should not reply on
the relationship. Even with a constraining mechanism in place it is best to
write SQL such that it explicitly targets the desired row set. This makes
the desired result clear to readers of the code who may not be aware of all
the system design features.
IMHO -- Mark D Powell --
-----Original Message-----
From: oracle-l-bounce@xxxxxxxxxxxxx
[mailto:oracle-l-bounce@xxxxxxxxxxxxx]On Behalf Of Stephen.Lee@xxxxxxxx
Sent: Tuesday, September 28, 2004 12:36 PM
To: jkstill@xxxxxxxxx
Cc: oracle-l@xxxxxxxxxxxxx
Subject: RE: Are these suppose to be equivalent?
I think there is a 1:1 relation. On the surface, based on what I know about
SQL, it would seem that if the subquery returns one value, then all columns
of planb_location would be set to that value; if the subquery returns
multiple values, the result should be an error(??). But there is no error.
It works, and the updates correspond correctly. So clearly, Oracle is
keeping the rows that match up "inside" the subquery properly matched up
"outside" the subquery. And now we are back to the whole issue of whether
there is an official inside and outside of the subquery. That is: Is the
update with the subquery SUPPOSED to work, or does it just happen to work
because of some subquery magic applied by Oracle.
>-----Original Message-----
>It would appear that statement #2 should update a subset of the data
>that statement #1 is updating.
>
>One exception would be if there is a 1:1 relation between location
>and planb_location.
--
//www.freelists.org/webpage/oracle-l
--
//www.freelists.org/webpage/oracle-l
Other related posts:
