update with a join

Hi,

[Quoted] I'm new to Oracle SQL. What I'm trying to do is this:

    UPDATE tbl1 t1

        SET t1.col0 = 0
        WHERE
[Quoted]             t1.col1, t1.col2 in (SELECT t2.col1, t2.col2 from tbl2 t2)

[Quoted] Both col1 and col2 are indexed. Basically, the two conditions on t1 are [Quoted] taken from the result set of the subquery. I couldn't get this to work [Quoted] unless I concatenate the two conditions into one single column as shown below:

[Quoted] [Quoted]     WHERE t1.col1 || t1.col2 in (SELECT t2.col1 || t2.col2 from tbl2 t2)

[Quoted] But concatenating the two columns prevented Oracle to use indexes. Does [Quoted] anyone know a better way to get this to work efficiently?

TIA Received on Wed Oct 25 2000 - 22:05:48 CEST