Re: update with a join

From: <lrako_at_my-deja.com>
Date: Thu, 26 Oct 2000 12:55:06 GMT
Message-ID: <8t99j7$c28$1@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 - 07:55:06 CDT