Re: update with a join

NoSpam wrote:

> 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

If col1 or col2 is can contain null values, then you may use something like this:

    UPDATE tbl1 t1

        SET t1.col0 = 0
        WHERE
            nvl(t1.col1,'-null-'), t1.col2 in (SELECT nvl(t2.col1,'-null-), t2.col2 from tbl2 t2)

Libor Received on Thu Oct 26 2000 - 03:34:29 CDT