Re: update with a join

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.