Re: How to update column in another database

From: <jhostetter_at_dande.com>
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:

  1. A database link to the remote database.
  2. 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

Original text of this message