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

The reason EXISTS is better than IN clause is that EXISTS is faster. Whenever the condition is met the first time, query execution will stop as opposed to IN clause.

Ram Lakshminarayanan wrote:

> 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 Fri Jul 27 2001 - 01:20:30 CDT