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

Instead of the Subquery containing the IN clause, please use EXISTS clause.

The query will look as follows: (please test it)

update test t1
set t1.id = 3
where exists (select t2.id from test t2, tablex x

           where t2.id = t1.id and t2.id = x.id)

atta ur-rehman wrote:

> hi essbee,
>
> i think a subquery should be able to achieve the same result. try this one:
>
> update test t1
> set t1.id = 3
> where t1.id in
> (select t2.id from test t2, tablex x
> where t2.id = t1.id and t2.id = x.id)
>
> you may need to change the IN clause with something more appropriate with your situation.
>
> hope that helps.
>
> :) ATTA
>
> "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)
Received on Thu Jul 26 2001 - 03:36:28 CDT