Re: How to update column in another database
Date: 1996/12/09
Message-ID: <58hgt3$dj2_at_lily.redrose.net>#1/1
In article <584p4g$a3v_at_dj.auc.edu>, usul_at_auctr.auc.edu (Ron McBay) wrote:
>I want to update the values in a column in a table in another database based
>on the values in a column in a table in a local database. Is this possible?
>I wanted to use a command like:
>
>UPDATE table1_at_db1 a, table2 b
> SET a.column1 = b.column2
> WHERE a.col_id = b.col_id
>
>That didn't seem to work and I don't know whether I'm just using the wrong
>syntax or whether what I want to do just isn't possible.
>
>Please advise.
>
>Thanks,
>
>Ron
You need two things:
- A database link to the remote database.
- The distributed option of Oracle, if you are going to update that remote database.
It looks like you are using a link to table1. You will not be able to update table1 without the distributed option. Also, I think you will have to modify your SQL statement to:
UPDATE table1_at_db1 a
SET a.column1 =
(SELECT b.column2 FROM table2 b WHERE b.col_id = a.col_id);
If you don't want to purchase the distributed option, run the SQL command on the remote database as:
UPDATE table1 a
SET a.column1 =
(SELECT b.column2 FROM table2_at_db2 b WHERE b.col_id = a.col_id);
Hope this helps.
Jay Hostetter
Database Administrator
D. and E. Communications
Ephrata, Pa. U.S.A
jhostetter_at_dande.com
Received on Mon Dec 09 1996 - 00:00:00 CET