Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations sizbut on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Merging in Oracle 8i 2

Status
Not open for further replies.

evgeniosc

IS-IT--Management
Oct 5, 2002
75
CY
I would like to load a txt file into an Oracle 8i table but like the Oracle 9i merge functionality.

e.g INSERT the new records
UPDATE the old records if it is needed

This is needed to maintain old records that have been inserted into the table but are not in the file now.

Some time ago I found a PL/SQL block that accomplished this but I lost the link.
 
I think the following would accomplish what you're looking for:

BEGIN
UPDATE my_table SET some_column = some_value
WHERE some_condition_is_true;
IF (sql%ROWCOUNT = 0) THEN
INSERT INTO my_table .......;
END IF;
END;
 
This answer works only for one record and not for two tables!
 
MERGE sql command works only in 9i+. In earlier versions you have to use pl/sql and make processing record-by-record. Alternatively if you have a PK on target table you may use EXCEPTION TABLE specified for that constraint to find "duplicates" and process them separately.

Regards, Dima
 
I'm not sure what you mean by "not for two tables". Your original post didn't mention anything about two tables; indeed "inserted into the table" cerainly seems to imply ONE table. But I'm sure that with a little thought you could expand the concept to take care of as many tables as you need.

As for multiple records - OK, let me be more explicit:

FOR i IN (SELECT A BUNCH OF RECORDS FROM HOWEVER MANY TABLES YOU WANT) LOOP
UPDATE my_table1 SET some_column = some_value
WHERE some_condition_is_true;
IF (sql%ROWCOUNT = 0) THEN
INSERT INTO my_table1 .......;
END IF;
UPDATE my_table2 SET some_column = some_value
WHERE some_condition_is_true;
IF (sql%ROWCOUNT = 0) THEN
INSERT INTO my_table3 .......;
END IF;

.
.
.
UPDATE my_table8059 SET some_column = some_value
WHERE some_condition_is_true;
IF (sql%ROWCOUNT = 0) THEN
INSERT INTO my_table95874 .......;
END IF;
END LOOP;

As for Dima repeating what you have said - I must have missed your mentioning "if you have a PK on target table you may use EXCEPTION TABLE specified for that constraint to find "duplicates" and process them separately. " This seems like a very clean and workable approach that you might be able to use (another star for you, Dima!).


 
Maybe my question was not so clear.
Anyway what I want is a method that works in Oracle 8i that does the same as the following command in Oracle 9i:

MERGE INTO ACCOUNTS acc
USING (SELECT * FROM ACCOUNTS_TEMP) tmp
ON (acc.ACCOUNTNUMBER=tmp.ACCOUNTNUMBER)
WHEN MATCHED THEN
UPDATE SET
acc.ACCOUNTTITLE = tmp.ACCOUNTTITLE
WHEN NOT MATCHED THEN
INSERT (acc.ACCOUNTNUMBER, acc.ACCOUNTTITLE)
VALUES (tmp.ACCOUNTNUMBER, tmp.ACCOUNTTITLE);
 
And apparently Dima and my responses were not explicit enough!

Code:
BEGIN
FOR tmp IN (SELECT * FROM ACCOUNTS_TEMP) LOOP
   UPDATE accounts 
      SET ACCOUNTTITLE = tmp.ACCOUNTTITLE
    WHERE ACCOUNTNUMBER=tmp.ACCOUNTNUMBER;
   IF (sql%ROWCOUNT = 0) THEN
     INSERT INTO accounts(ACCOUNTNUMBER, ACCOUNTTITLE)
     VALUES (tmp.ACCOUNTNUMBER, tmp.ACCOUNTTITLE);
   END IF;
END LOOP;
END;
 
Thanks a lot carp.

Sorry but your responses were not explicit enough because i forgot to mention that I know SQL very well but I don't know PL/SQL !
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top