update with a join

From: NoSpam <NoSpam_at_NoSpam.com>
Date: Wed, 25 Oct 2000 16:05:48 -0400
Message-ID: <8t76rf$f1b$1@ih292.ea.unisys.com>

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 Received on Wed Oct 25 2000 - 15:05:48 CDT