Home » SQL & PL/SQL » SQL & PL/SQL » utility which selectively updates/inserts a record for given fields
utility which selectively updates/inserts a record for given fields [message #37995] Wed, 13 March 2002 00:39 Go to next message
gnana
Messages: 1
Registered: March 2002
Junior Member
There are 2 databases DB1 and DB2.
There is a table temp1 in DB1 as well as in DB2 with fields as

field_key (primary key) (char(2))
field_1 (char(1))
field_2 (char(2))
field_3 (char(3))
field_4 (char (4))

There are some records in the table temp1 in both the databases (DB1 and DB2).
Some of the records (as far as primary key is concerned) are duplicates in DB1 and DB2 databases.

I want all the records of this table to be inserted from DB1 to DB2 such that if the record corresponding to the primary key field(field_key) in DB1 is not there in DB2, then the record be directly inserted to DB2 else for this duplicate record the field values for the fields (field_3 and field_4) be updated in DB2 corresponding to that record.

Just for an example, suppose the table temp1 has rown in DB1 as
AA, N, Y, X, Z
AB, N, N, C, Z
AC, Y, N, Z, X
DD, L, S, Q, R

and in DB2 , the rows are

AB, N, N, C, D
BC, Y, N, M, L
DD, K, Q, N, N

then the final temp1 in DB2 should have the following rows::
AA, N, Y, X, Z
AB, N, N, C, Z
AC, Y, N, Z, X
BC, Y, N, M, L
DD, K, Q, Q, R

(Note that in the final result, the values corresponding to the rows AB, DD for the fields field_3 and field_4 has been taken from DB1 database)

I know the above can be done using PL/SQL. But, I would like to know if there is any ORACLE utility which can be directly used to perform this activity(i.e a utility which selectively updates/inserts a record for given fields)

Report message to a moderator

Re: utility which selectively updates/inserts a record for given fields [message #38001 is a reply to message #37995] Wed, 13 March 2002 01:22 Go to previous message
pratap kumar tripathy
Messages: 660
Registered: January 2002
Senior Member
u can do it in 2-sql statement

insert into temp1@db2_link_name
select * from
temp1@db1_link_name
where field_key1 not in
(select field_key1 from temp1@db2_link_name)

same way u can do for the update

Report message to a moderator

Previous Topic: How to select n-1 columns in a table with n columns ?
Next Topic: passing blanks
Goto Forum:
  

[ Syndicate this forum (XML) ] [ RSS ]

Current Time: Fri Apr 19 21:51:01 CDT 2024
.:: Forum Home :: Blogger Home :: Wiki Home :: Contact :: Privacy ::.