Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.misc -> Re: Updating correlated subqueries

Re: Updating correlated subqueries

From: dean <deanbrown3d_at_yahoo.com>
Date: 31 Jan 2007 21:08:20 -0800
Message-ID: <1170306500.434924.137150@v33g2000cwv.googlegroups.com>


On Jan 26, 9:39 pm, "Charles Hooper" <hooperc2..._at_yahoo.com> wrote:
> On Jan 26, 1:57 pm, "dean" <deanbrow..._at_yahoo.com> wrote:
>
>
>
>
>
> > create table T (F1 number, F2 number);
> > create table U (F1 number, F2 number);
>
> > insert into T values (1,1);
> > insert into T values (2,2);
> > insert into T values (3,3);
> > insert into T values (4,4);
>
> > insert into U values (1,111);
> > insert into U values (2,222);
>
> > commit;
>
> > select * from T;
>
> > F1 F2
> > ---------- ----------
> > 1 1
> > 2 2
> > 3 3
> > 4 4
>
> > 4 rows selected.
>
> > --UPDATE QUERY
> > update T
> > set F2 =
> > (
> > select F2
> > from U
> > where U.F1 = T.F1
> > )
> > where T.F1 in (select F1 from U);
>
> > 2 rows updated.
>
> > select * from T;
>
> > F1 F2
> > ---------- ----------
> > 1 111
> > 2 222
> > 3 3
> > 4 4
>
> > 4 rows selected.
>
> > Ok, given the above simplified update on a correlated query, is there
> > any other way that might be more efficient that using the IN clause for
> > the WHERE statement? Basically I only want to update the values where
> > there is a match.
>
> > Thanks!
>
> > Dean
> > Oracle 9.2, 10.1, Windows.
>
> Something like this will likely work:
> UPDATE
> T
> SET
> F2=NVL(
> (SELECT DISTINCT
> F2
> FROM
> U
> WHERE
> U.F1=T.F1),
> F2);
>
> 4 ROWS UPDATED
>
> SELECT
> *
> FROM
> T;
>
> F1 F2
> ---------------------------
> 1 111
> 2 222
> 3 3
> 4 4
>
> Note the side effects: inaccurate count of the number of rows updated,
> as well as increased redo and undo. In the above SQL statement, if the
> value returned by the subquery is NULL, the original value of T.F2 is
> written over the top of the existing, original value of T.F2. The
> original solution that you posted is a better solution than the
> shortcut solution that I posted due to the side effects.
>
> Charles Hooper
> PC Support Specialist
> K&M Machine-Fabricating, Inc.- Hide quoted text -
>
> - Show quoted text -

Interesting. Maybe a where clause could update only the records that are actually different? I wonder why oracle does not provide a simple syntax for such an obvious task, maybe in the next release, who knows.

Thanks for the help,

Dean Received on Wed Jan 31 2007 - 23:08:20 CST

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US