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

 You can always use subqueries Esbee. I'm too tired (and bored actually) to  try this out on my database, but something like the following, using an  "exists" statement will surelly work:

 UPDATE TABLE_A A
  SET TABLE_A.my_col = "thisThat"
  WHERE exists

     (select 1
         from table_B   B
         where B.col_b = a.col_a
             and b.col_c = a.col_d);

 Of course you can also use a subquery to return a value to your update  statement, like:

 update table_A a

     set field1 = (select b.field1
                            from table_B b
                             where b.field2 = a.field2
                              etc
                                );

 I hope you can understand my simple example. Keep trying and be creative.  Oracle can do just about everything

 yannis markakis

>
>
> ----- Original Message -----
> From: "change" <changeme_at_changeme>
> Newsgroups:
>

 comp.databases.oracle,comp.databases.oracle.misc,comp.databases.oracle.tools
> ,comp.databases.oracle.server
> Sent: Friday, May 25, 2001 2:39 AM
> Subject: Re: Can you specify multiple tables in update's where clause?
>
>
> > Proclaimed Hans from the mountaintop...
> > "Hans Noordhof" <kinetics.delete.or.remove_at_dds.nl> wrote in message
> > news:9eifm3$6vfma$1_at_reader01.wxs.nl...
> > > Try this <SNIP>
> > > update table_a
> > > <SNIP>
> > > And buy an SQL-book.
> > >
> > > change <changeme_at_changeme> schreef in berichtnieuws
> > > 9ei2f5$dh01_at_kcweb01.netnews.att.com...
> >
> > Hans,
> >
> > Firstly, thanks for your reply!
> >
> > Secondly, do not be condescending in your future replies to the Usenet.
> > "Buy a SQL book??!"
> >
> > I did quite a bit of research before turning to Usenet for an answer. I
 have
> > 5 of the most popular SQL books here with me, none of which has more
 than
 3
> > pages of description (and flow diagrams) on the syntax of Update.
 Needless
> > to say, I could not find an answer to my query.
> >
> > By the way, the better alternative (thinking outside of the SQL box
 here)
> > would be use a cursor formed by the outer query, and iterating over the
> > returned rows to update the desired attribute.
> >
> > In all fairness, people probably do jump into the Usenet with their
 question
> > without adequate reserach, but please be humble, for in replying to it
 you
> > are only increasing your knowledge pool. Please be humble, it only
 reduces
> > the garbage you type! :-D
> >
> > essbee.
> >
> > > > 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 Sat Jul 21 2001 - 16:08:47 CDT