Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.misc -> Re: Update a Table from a Second Table

Re: Update a Table from a Second Table

From: Sybrand Bakker <postbus_at_sybrandb.demon.nl>
Date: Sat, 28 Jun 2003 11:10:12 +0200
Message-ID: <vfthg4qk143f30@corp.supernews.com>

"Greg" <ghvance_at_yahoo.com> wrote in message news:102c7417.0306271253.216a2b80_at_posting.google.com...
> This is an easy way to update a table from a second table in Sql
> Server.
>
> update table1
> set table1.col2 = table2.col2
> from table1, table2
> where table1.keycol = table2.keycol
>
> I know you can do something like this in Oracle:
>
> update table1 a
> set table1.col2 = (select col2 from table2 where table2.keycol =
> a.keycol)
>
> but that example will update every row in table1, not just the ones
> that match the rows in table2.
>
> The sql server version not only gets the values it needs from the
> second table, it also limits the rows updated to those the matching
> rows in table2.
>
> Is there an easy way to do this in Oracle?
>
> thanks much

I second the comments of Daniel Morgan. Almost everyone here exposed to SQLserver starts asking questions in any (and too frequently:in all) Oracle newsgroups, prior to doing the RTFM routine.

As to your question
 update table1 a
 set table1.col2 = (select col2 from table2 where table2.keycol =  a.keycol)
where exists
(select 'x'
 from table2
 where keycol = a.keycol
)

Is that sooo difficult? IMO it is not.
Also in 8i and higher you can

 update
(select table2.col2
 from table1, table2
 where table1.keycol = table2.keycol
)
set table1.col2 = table2.col2

And of course that is documented, it only requires a little RTFM

Something most SQLserver people are obviously not prepared to.

-- 
Sybrand Bakker
Senior Oracle DBA

to reply remove '-verwijderdit' from my e-mail address
Received on Sat Jun 28 2003 - 04:10:12 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US