Re: update with a join
Date: Thu, 26 Oct 2000 12:55:06 GMT
Message-ID: <8t99j7$c28$1_at_nnrp1.deja.com>
In article <8t7icv$1b0$1_at_nnrp1.deja.com>,
sybrandb_at_my-deja.com wrote:
> In article <8t76rf$f1b$1_at_ih292.ea.unisys.com>,
> "NoSpam" <NoSpam_at_NoSpam.com> wrote:
> > Hi,
> >
> > I'm new to Oracle SQL. What I'm trying to do is this:
> >
> > UPDATE tbl1 t1
> > SET t1.col0 = 0
> > WHERE
> > t1.col1, t1.col2 in (SELECT t2.col1, t2.col2 from tbl2
t2)
> >
> > Both col1 and col2 are indexed. Basically, the two conditions on t1
are
> > taken from the result set of the subquery. I couldn't get this to
work
> > unless I concatenate the two conditions into one single column as
shown
> > below:
> >
> > WHERE t1.col1 || t1.col2 in (SELECT t2.col1 || t2.col2 from tbl2
t2)
> >
> > But concatenating the two columns prevented Oracle to use indexes.
Does
> > anyone know a better way to get this to work efficiently?
> >
> > TIA
> >
> >
> The correct syntax for a subquery on multiple columns is
> UPDATE tbl1 t1
> SET t1.col0 = 0
> WHERE
> (t1.col1, t1.col2)
> in
> (SELECT t2.col1, t2.col2 from tbl2 t2)
>
> Performance wise you would better
> update tbl1 t1
> set t1.col0 = 0
> where exists
> (select 'x'
> from tbl2 t2
> where t2.col1 = t1.col1
> and t2.col2 = t1.col2)
*****>> Absolutely right. Avoid the use of (NOT) IN and replace it with EXISTS whenever possible.
> or (8.0 and higher only)
> update
> (select t1.col0
> from tbl1 t1, tbl2 t2
> where t2.col1 = t1.col1
> and t2.col2 = t2.col2)
> set col0 = 0
>
> Hth,
>
> --
> Sybrand Bakker, Oracle DBA
>
> All standard disclaimers apply
> ----------------------------------------------------------------------
--
>
> Sent via Deja.com http://www.deja.com/
> Before you buy.
>
Sent via Deja.com http://www.deja.com/
Before you buy.
Received on Thu Oct 26 2000 - 14:55:06 CEST