Import: update existing records 1

[abbath8]

Hi,

The question is: how to use import to update existing records?
I used the import with the ignore=yes option, and this was good for not to show errors on existing tables and only the new data was imported. But now I want the existing records updated too. How to do this?

 

[sem]

There's no way to UPDATE existing records by imp utility. You may load table data to some other table ( e.g. in different schema) and then use this data to update existing rows.

Regards, Dima

 

[ddrillich]

As Dima suggested, first load the data into a temporary table. Then you can use the new Oracle 9i MERGE command to either perform an UPDATE or INSERT.

For more information, please look at:

http://download-west.oracle.com/docs/cd/B10501_01/server.920/a96540/statements_915a.htm#2090700

MERGE INTO bonuses D
USING (SELECT employee_id, salary, department_id FROM employees
WHERE department_id = 80) S
ON (D.employee_id = S.employee_id)
WHEN MATCHED THEN UPDATE SET D.bonus = D.bonus + S.salary*.01
WHEN NOT MATCHED THEN INSERT (D.employee_id, D.bonus)
VALUES (S.employee_id, S.salary*0.1);

Regards,
Dan

 

[abbath8]

Thank you so much