Re: Update a Table from a Second Table

"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