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

Home -> Community -> Usenet -> c.d.o.tools -> Re: update with a join

Re: update with a join

From: <sybrandb_at_my-deja.com>
Date: Wed, 25 Oct 2000 21:13:10 GMT
Message-ID: <8t7icv$1b0$1@nnrp1.deja.com>

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)

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.
Received on Wed Oct 25 2000 - 16:13:10 CDT

Original text of this message

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