Home » RDBMS Server » Server Utilities » Update through SQL*loader
Update through SQL*loader [message #208737] Tue, 12 December 2006 00:38 Go to next message
Messages: 7062
Registered: December 2001
Senior Member
received via email
Can you help me.

I want to post the issue of SQL Loader

I have a problem here one of our customer wants to change all his depart names from old to new one. As they have changed the company name.
They have provide us old and new names txt file like

Old Names                       New Names
Asc-data-south-r01              asc-logic-south-r01
Asc-data-west-r01               asc-logic-west-r01
Asc-data-east-r01               asc-logic-east-r01
Asc-data-north-r01              asc-logic-north-r01

I have to update few tables. I can't do manually as they have 400 departments.
I am reading SQL Loader and have used SQL Loader in past ones. But don't know how to update the table with new names and leaving rest of the data intact.
I am new to Oracle.

Please help me
Thanks in advance.

Re: Update through SQL*loader [message #208824 is a reply to message #208737] Tue, 12 December 2006 05:44 Go to previous messageGo to next message
Mahesh Rajendran
Messages: 10672
Registered: March 2002
Location: oracleDocoVille
Senior Member
Account Moderator
SQL*Loader cannot update directly.
The standard workaround is to just load the data as is into a staging table and user MERGE statements as required.
Re: Update through SQL*loader [message #209205 is a reply to message #208824] Wed, 13 December 2006 14:48 Go to previous messageGo to next message
Messages: 20849
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Also, if your database version supports it, you could use this text file as an external table and do the update.
Re: Update through SQL*loader [message #209213 is a reply to message #209205] Wed, 13 December 2006 17:19 Go to previous message
Messages: 1
Registered: December 2006
Junior Member
Here is what I would suggest:
a. Create a temporary table in your database with the old vs new mapping
b. Upload the mapping information to database using sql*Loader
c. Use a simple update statement as below:
update emp
set emp.deptno = (select mpg.b from new_mapping_table mpg
where mpg.a = emp.deptno)

Hope this helps you.
Previous Topic: impdp errors when moving database to new server
Goto Forum:

Current Time: Fri Oct 21 17:55:24 CDT 2016

Total time taken to generate the page: 0.28903 seconds