CODEWITHSUNDEEP
sqloracle-databaseoracle10gsql-update
gospodin
gospodin

Reputation: 1201

Oracle update table with the value from another database

In one db i have table PRODUCTS with columns NAME and TECHNICAL_NAME.

In second database I have table TEMP_PRODUCTS with columns NAME (that corresponds to column NAME of the table PRODUCTS from the 1st db) and TECHNICAL_NAME that is null and that should be updated with the corresponding TECHNICAL_NAME values from 1st db.

So I should do UPDATE table TEMP_PRODUCTS by using JOIN with columns NAME?

I would like to avoid solution with exporting table from 1st bd and importing it to the 2nd db.

How can I do this?

Upvotes: 1

Views: 13346

Answers (3)

Dileep
Dileep

Reputation: 664

By Creating the DB Link only we can access the one DB objects from another DB.

CREATE DATABASE LINK db1_link 
   CONNECT TO <User Name> IDENTIFIED BY <pwd>
   USING 'db2';

-- db2 means Service Name for exp products db

Then Update Statement 

UPDATE temp_products tp
  SET technical_name=
    (SELECT technical_name FROM products@db1_link p
      WHERE tp.name = p.name)

Upvotes: 4

Passerby
Passerby

Reputation: 10080

Assuming:

  • both NAME columns are UNIQUE;
  • you have a database link in db2 pointing to db1.
UPDATE temp_products tp
  SET technical_name=
    (SELECT technical_name FROM products@db1 p
      WHERE tp=name=p.name)

Upvotes: 3

Gaurav Soni
Gaurav Soni

Reputation: 6346

You need to execute the below query in db2 ,where temp_product table lies,and db link db1 on the same database db2,to connect db1.

   MERGE temp_products tp
     USING products@db1 pp
     ON(tp.name = pp.name)
   WHEN MATCHED THEN
     UPDATE SET tp.technical_name = pp.technical_name;

You need to create a db link in database db2,so that you can connect to db1 ,to acces product table. Please find the syntax for creating db link

CREATE [PUBLIC] DATABASE LINK <link_name>
CONNECT TO <user_name>
IDENTIFIED BY <password>
USING '<service_name>';

Upvotes: 2

Related Questions