Replace part of oracle database with data in excel

Question

I have a table with 103 columns and more than 100,000 records. I was told that Oracle Data base has bad data in few of the columns(four columns) and in few of the records(20,000 records approx) and was told to modify data. Also I was given a query where I should modify data(bad data). With the help of query, I exported all the bad data into excel and modified using macros.

How to replace existing bad data in Oracle data base to data which I have in excel(good data). Is it possible to modify data in some part of the database using SQL query? I mean to say 4 out of 103 columns and 20k out of 100k records has to be modified without affecting already existing good data in Oracle database.

I am using SQL developer and Oracle 11g

My query to retrieve bad data

select e.id_number
, e.gender_code
, e.pref_mail_name
, e.prefix
,e.first_name
,e.last_name
,e.spouse_name
,e.spouse_id_number
,e.pref_jnt_mail_name1
,e.pref_jnt_mail_name2                                                                      
from advance.entity e                                                                        
where e.person_or_org = 'P'                                                                   
and ascii(e.spouse_name) <> 32                                                               
or ascii(e.spouse_id_number) <> 32"

Note: Moreover I am not changing any primary key or secondary key data. Bad data is in other columns.

I think you're looking for the `UPDATE` statement. [Documentation here](http://docs.oracle.com/cd/B28359_01/server.111/b28286/statements_10008.htm#SQLRF01708). With a bit of work with Excel formulas it's possible to generate the required `UPDATE` statements in the Excel spreadsheet, then copy these over to SQL Developer (or whatever tool you have) and execute them. Best of luck. — Bob Jarvis - Слава Україні, Jul 08 '14 at 02:02

score 0 · Answer 1 · edited May 23 '17 at 12:11

You can start by importing all corrected data (which now resides in your Excel file), to a temporary table. See Load Excel data sheet to Oracle database for more details.

The it should be a simple task to write one or more UPDATE statements. If you are new to Oracle syntax, Tech on the Net has some nice examples.

Also, do not forget to backup the original table to a temporary table before you make any changes there. That way, if you mess up the data repair, you can start over.

Replace part of oracle database with data in excel

1 Answers1