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. 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 - Слава Україні
– Bob Jarvis - Слава Україні, Commented Jul 8, 2014 at 2:02

Community · Accepted Answer · 2017-05-23 12:11:01Z

0

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.

edited May 23, 2017 at 12:11

CommunityBot

11 silver badge

answered Jul 8, 2014 at 2:47

sampathsris

22.4k12 gold badges74 silver badges103 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Replace part of oracle database with data in excel

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related