Re: What is the intermdiate dataset in this sql?

"deanbrown3d" <deanbrown3d_at_yahoo.com> wrote in message news:1119681934.842441.98290_at_z14g2000cwz.googlegroups.com...
> What is the intermediate dataset built in this statement:
>
> ----------------------------------------------------
> update T1
> set T1.C1 =
> (
> select T2.C1 from T2
> where T1.C2 = T2.C2
> and T1.C3 = 123
> )
> ----------------------------------------------------

All rows in the T1 table will be updated. Column T1.C1 will be set to null for rows where T1.C3<>123 or T1.C3 is null. Seems it will be far away from result You expected.

>
> And is there any difference between this one and:
>
> ----------------------------------------------------
> update (select * from T1 where C3 = 123)
> set T1.C1 =
> (
> select T2.C1 from T2
> where T1.C2 = T2.C2
> )
> ----------------------------------------------------

Only rows with T1.C3=123 will be updated. But you should use alias for the subquery or you reach syntax error.
Something like:
update (select * from T1 where C3 = 123) Z set Z.C1 =
(
  select T2.C1 from T2
  where Z.C2 = T2.C2
)

>
> and
>
> ----------------------------------------------------
> update T1
> set T1.C1 =
> (
> select T2.C1 from
> T2 inner join T1
> on T1.C2 = T2.C2
> where T1.C3 = 123
> )
>
> ----------------------------------------------------

Very likely You will reach ORA-01427 error: single-row subquery returns more than one row for both 3-rd and 4-th statements... If You have luck and C2 column in both T1 and T2 tables has unique values, all rows in the T1 will be updated in the 3-rd statement and only rows where T1.C1=123 will be updated by 4-th statement...
>
> and
>
> ----------------------------------------------------
> update T1
> set T1.C1 =
> (
> select T2.C1 from
> T2 inner join T1
> on T1.C2 = T2.C2
> )
> where T1.C3 = 123
>
> ----------------------------------------------------
> Thanks for any help. I know there are several people I work with that
> are unclear on this too.

IMHO You should use something like:
update T1 set T1.C1=(select T2.C1 from T2 where T2.C2=T1.C2)   where T1.C3=123

And RTFM Oracle Concepts and SQL reference....

>
> Thanks
>
> Dean
>

Dmitry Loginov Received on Mon Jul 04 2005 - 03:37:48 CDT