Re: Can you specify multiple tables in update's where clause?

"change" <changeme_at_changeme> wrote in message news:<9ei2f5$dh01_at_kcweb01.netnews.att.com>...
> Need some sql expert input on UPDATE command. How do I involve multiple
> tables in WHERE clause?
>
> Consider this simplified example on update command:
>
> UPDATE TABLE_A
> SET TABLE_A.my_col = "thisThat"
> WHERE
> TABLE_A.col_a = TABLE_X.col_b
> and TABLE_X.col_c = TABLE_Y.col_d
>
>
> This fails with a message that table2 is not in scope! How do I involve
> multiple tables in the where clause of an update? Can't seem to find an
> example in any book/webpage.
>
> Comments?
>
> essbee
>
> (I am using Oracle 8.1.5, though this seems to be a generic SQL question)

You need to code the update something like update table_a A
set A.my_col = 'whatever'
where A.test = ( select B.test

                 from   table_b B, table_c C
                 where  B.key = C.key
                 and    B.somecol = A.somecol
                )
and exists ( select D.test
             from table_b D, table_c E
             where D.key = E.key
             and   D.somecol = A.somecol
            );

The column A.my_col will be set to null if the join returns no rows so that is why you code an exists on the same condition. Obviously if the there was no coordinating where clause condition and multiple rows were going to be returned by the sub-query then an 'in' clause would be used. This isn't a great example, but it should give you the idea.

• Mark D Powell --