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

This is a handy trick and is a bit closer to the original poster's syntax. It sets up an inline view that is a join and then updates that view. Note that the id columns in both tables are primary keys and rows that don't match in the join are not updated (i.e. equivalent to a "where exists clause"). It is not as flexible as a correlated subquery but sometimes is just the ticket.

Gene Hubert

SQL> desc g1

 Name                            Null?    Type
 ------------------------------- -------- ----
 ID                              NOT NULL NUMBER
 VAL                                      NUMBER

SQL> desc g2
 Name                            Null?    Type
 ------------------------------- -------- ----
 ID                              NOT NULL NUMBER

SQL> select * from g1;

        ID VAL
---------- ----------

         1          4
         2          5

SQL> select * from g2;

        ID

         1
         2
         3

SQL> update (select a.id as id1, a.val as val1, b.id as id2   2 from g1 a, g2 b
  3 where a.id = b.id) set id2 = val1;

2 rows updated.

SQL> select * from g2;

        ID

         4
         5
         3

---------------------------------------------------------

Ram Lakshminarayanan <raml_at_india.hp.com> wrote in message news:<3B61082E.229BC55E_at_india.hp.com>...
> 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 Mon Jul 30 2001 - 19:41:15 CEST