Beefy Boxes and Bandwidth Generously Provided by pair Networks
Perl Monk, Perl Meditation
 
PerlMonks  

Oracle mass update script request.

by dbmathis (Scribe)
on Jul 28, 2008 at 01:48 UTC ( #700438=perlquestion: print w/replies, xml ) Need Help??

dbmathis has asked for the wisdom of the Perl Monks concerning the following question:

Hi,

I have been given a critical task of updating 45000 records on sensitive oracle database and although it is a simple update, I am not to familiar with writing update scripts in perl. I normally just do selects and then do data analysis

I am looking for a full script example using DBI that will update the records and keep a log of everything that gets updated.

My update statement is the following:
update ( select t1.field1, 999 deact_status from schema.table1 t1 inner join schema.table2 t2 on t1.id = t2.id where t2.field2 = $emp_id ) set field1 = deact_status
And the 45000 emp_ids exist in a flat file in the following format which is an emp_id per line.
0909099 3298729 2849872 2472972 ... ... ...

I am sure its as easy as just loooping through the file one emp_id at a time and then plugging the emp_id into the update statement and running it, however I want to learn how to do this the right way at the beginning from the pros.

After all this is over, all that will really have mattered is how we treated each other.

Replies are listed 'Best First'.
Re: Oracle mass update script request.
by Gangabass (Vicar) on Jul 28, 2008 at 02:00 UTC

    You right about looping. Just use placeholders.

    So:

    #connect to DB ....... #prepare your update statement ....... #YOUR LOOP START #read line from your file (via while for example) #execute your statement #YOUR LOOP END
Re: Oracle mass update script request.
by TedPride (Priest) on Jul 28, 2008 at 04:06 UTC
    Assuming your update statement is correct, given your database structure and Oracle (I'm more familiar with mySQL):
    use DBI; use strict; my $user = 'user'; my $pass = 'pass'; my $db = 'database'; my $filename = 'filename.txt'; my $query = qq* update ( select t1.field1, 999 deact_status from schema.table1 t1 inner join schema.table2 t2 on t1.id = t2.id where t2.field2 = ? ) set field1 = deact_status *; my ($dbh, $sth, $handle); $dbh = DBI->connect("dbi:Oracle:$db", $user, $pass) || die "Couldn't connect to database: " . DBI->errstr; open ($handle, $filename) || die "Couldn't open $filename for read.\n"; while (<$handle>) { chomp; $sth = $dbh->prepare_cached($query, {}, 1); $sth->execute($_); } $dbh->disconnect();
    This is untested, but ought to work in theory.
Re: Oracle mass update script request.
by grinder (Bishop) on Jul 28, 2008 at 10:21 UTC

    Why do you want to make life so miserable for yourself? Why not ask the database to do the update for you in one hit.

    update table1 t1 set t1.field1 = 999 where exists ( select 1 from table2 t2 where t1.id = t2.id )

    Then you just have to debug your SQL. No Perl needed apart from a $db->do(''), no bugs to look for! Not to mention faster...

    • another intruder with the mooring in the heart of the Perl

      grinder,

      I must be missing something. Are you saying I can update 45000 records all at once? Were does the sql get the 45000 records from when they only exist in a flat text file?

      I agree that the easiest solution is always the best solution. Can you please elaborate?

      Best Regards

      After all this is over, all that will really have mattered is how we treated each other.

Log In?
Username:
Password:

What's my password?
Create A New User
Node Status?
node history
Node Type: perlquestion [id://700438]
Approved by GrandFather
help
Chatterbox?
and the web crawler heard nothing...

How do I use this? | Other CB clients
Other Users?
Others pondering the Monastery: (4)
As of 2021-05-15 08:58 GMT
Sections?
Information?
Find Nodes?
Leftovers?
    Voting Booth?
    Perl 7 will be out ...





    Results (150 votes). Check out past polls.

    Notices?