The "where COL in (select ...)" is of course also correct (I used "where =
(exists ...)")
The general rule of thumb is to use "IN" if the subquery is small, and =
EXISTS if the results of the IN subquery would be large and the table in =
the subquery has a usable index on the matching column.
You should try both ways on your system and see which query works best.
> -----Original Message-----
> From: oracle-l-bounce_at_freelists.org
> [mailto:oracle-l-bounce_at_freelists.org]On Behalf Of system manager
> Sent: mercredi, 11. f=E9vrier 2004 13:00
> To: oracle-l_at_freelists.org
> Subject: Re:RE: JOINED UPDATE
>=20
>=20
> thank you very very much. =20
>=20
> update account A
> set (A.expire_dt, A.curr_eff_dt, A.acct_end_dt)
> =3D
> (select T.expire_dt, T.curr_eff_dt, T.acct_end_dt from=20
> temp_savedates T
> where T.acct_num=3DA.acct_num)
> where A.acct_num in (select T.acct_num from temp_savedates T);
>=20
> ----------------------------------------------
> Original Message
> From: "Jacques Kilchoer"<Jacques.Kilchoer_at_quest.com>
> Subject: RE: JOINED UPDATE
> Date: Wed, 11 Feb 2004 12:13:46 -0800
>=20
> >The syntax you have below will cause an error in Oracle.
> >
> >Oracle version of your statement would be:
> >update account
> >set (account.expire_dt,
> > account.curr_eff_dt,
> > account.acct_end_dt) =3D3D (select temp_savedates.expire_dt,
> > temp_savedates.curr_eff_dt,
> > temp_savedates.acct_end_dt
> > from temp_savedates
> > where account.acct_num =3D3D temp_savedates.acct_num)
> >where exists
> >(select * from temp_savedates
> > where account.acct_num =3D3D temp_savedates.acct_num) ;
Please see the official ORACLE-L FAQ:
http://www.orafaq.com
To unsubscribe send email to: oracle-l-request_at_freelists.org
put 'unsubscribe' in the subject line.
--
Archives are at http://www.freelists.org/archives/oracle-l/
FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html
-----------------------------------------------------------------
Received on Wed Feb 11 2004 - 17:22:18 CST
