Beefy Boxes and Bandwidth Generously Provided by pair Networks
The stupid question is the question not asked
 
PerlMonks  

Re^3: DBI: How to update 150K records efficiently

by jhourcle (Prior)
on Apr 01, 2008 at 15:32 UTC ( #677775=note: print w/ replies, xml ) Need Help??


in reply to Re^2: DBI: How to update 150K records efficiently
in thread DBI: How to update 150K records efficiently

Although the primary use is insertion, they can be used for updates ... mysql directly, oracle in a more round-about way.

The 'REPLACE' keyword in mysql's 'LOAD DATA' comment is record-by-record, based on the primary key of the table. If you're using autoincrement for for primary key, this isn't going to work for you. In the example given, however, there's a WHERE clause using the field 'vc', which I assumed was the primary key, or at least a unique index.

You're right that sqlldr doesn't handle this case. (its REPLACE keyword will remove the entire table, as will TRUNCATE. The only other option for tables w/ existing data is 'APPEND'.) However, you can use sqlldr to get the data into a temp table, and then use a pl/sql command to replace the records as needed (or insert into the temp tables those that don't have a corresponding record already). You'd have to benchmark it to see what's the best method for your specific situation.

If you're going to be designing a table that requires regular bulk updates, I'd highly recommend finding a suitable key (even if it's a composite key) and not use a sequence or autoincrement


Comment on Re^3: DBI: How to update 150K records efficiently

Log In?
Username:
Password:

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

How do I use this? | Other CB clients
Other Users?
Others wandering the Monastery: (11)
As of 2014-09-18 14:58 GMT
Sections?
Information?
Find Nodes?
Leftovers?
    Voting Booth?

    How do you remember the number of days in each month?











    Results (116 votes), past polls