Re: Update statment in Oracle

"Adam Boczek" <adam.boczek_at_NO_SPAM_gft-solutions.de> wrote in message news:998905644.129215_at_tux2.ham.acs-gmbh.de...
> I have two tables:
> table1 with fields "id" and "x"
> table2 with fields "id" and "x"
>
> I'd like to update value of field "x" from table1 with value of field "x"
> from table2 where "id" of table1 = "id" od table2. I've try this
statement:
>
> UPDATE table1
> SET table1.x = table2.x
> WHERE table1.id = table.id;
>
> but I always receive error that the name of feld is not correct. How can
> write such easy statement? In Access I've used "left join" and so on
without
> any problems...
> Help
>
> Greets,
> Adam
>
> -------------------------------------------
> Adam Boczek
> IT Consultant
> GFT Solutions GmbH
> adam.boczek_at_gft-solutions.de
> -------------------------------------------
>
>
>

Oracle is just not Access, it is much more.

Two solutions (and I don't know which one applies, as it appears to be too difficult for you to post platform and version)

this one works for all versions

update table1
set table1. =
(select table2.x
  from table2 where table2.id = table1.id) where
exists
(select 'x'
  from table2
  where table2.id = table1.id)

this one works on 8i and higher
update
(select table1.x, table2.x
 from table1, table2
 where table1.id = table2.id)
set table1.id = table2.id

Hth,

Sybrand Bakker,

Senior Oracle DBA Received on Mon Aug 27 2001 - 05:10:14 CDT