UPDATE TABLE USING DBLINK FROM REMOTE DATABASE

To update a table remotely from a destination database using source table, dblink can be used

Here i create a dblink called dbl to pipe the connection from destination to source.

SQL> create database link dbl connect to test identified by "password" using 'db                         emt';

Database link created.

SQL> select * from dual@dbl
  2  ;

D
-
X

SQL> create table t1 as select * from TEST.BIG_TABLE@dbl;

Table created.

Data can be remotely updated using UAS(update as select) like this

SQL> update t1 tab1
  2  set (OWNER,OBJECT_NAME) = (select bt.owner,bt.object_name
  3                                    from TEST.BIG_TABLE@dbl bt
  4                             where tab1.id = bt.id);

10000 rows updated.

SQL> commit;

Commit complete.

SQL> update t1 tab1
  2  set (OWNER,OBJECT_NAME) = (select bt.owner,bt.object_name
  3                                    from TEST.BIG_TABLE@dbl bt
  4                             where tab1.id = bt.id)
  5  where id < 100;

99 rows updated.

SQL> commit;

Commit complete.

Leave a Reply