Beefy Boxes and Bandwidth Generously Provided by pair Networks
go ahead... be a heretic

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?

What's my password?
Create A New User
Node Status?
node history
Node Type: note [id://677775]
and all is quiet...

How do I use this? | Other CB clients
Other Users?
Others perusing the Monastery: (4)
As of 2018-05-26 08:33 GMT
Find Nodes?
    Voting Booth?