Take the 2-minute tour ×

I need to do a mass update of a table (~30,000 records) in an Oracle 10g database. The challenge is there is no "where" clause that can select the target rows. Each target row can, however, be identified via a composite key. The catch here is that the list of composite keys is from an external source (not in the database).

Currently I have a Java program that loops through the list of composite keys and spits out a PL/SQL procedure which is essentially just a bunch of repeated update statements similar to the following:

update table1 t set myfield='Updated' where t.comp_key1='12345' and t.comp_key2='98765';

Is there a better way to do this, or is this "good enough" considering we're only dealing with ~30K records?

share|improve this question
You can do a bunch at a time using something like ... where (t.comp_key1, t.comp_key2) in (('12345', '98765'), ('11111', '11111'), ('22222', '22222')). The number of rows you can update will depend on the max length for a statement (which will be determined by your Java data driver), but you should be able to get 100 or more rows per statement. It's not beautiful but you'd save a lot of round trips. –  Ed Gibbs Apr 18 '13 at 18:22
Alternatively you could parse the entire lot into the database and use a table function. –  Ben Apr 18 '13 at 18:35

2 Answers 2

up vote 2 down vote accepted

Good enough.

30,000 updates using the primary key, even if they are all hard-parsed, will normally only take a few seconds. You could probably speed things up by combining the updates, as @Ed Gibbs suggested. But so far this looks like a very quick process that isn't worth optimizing. Putting it all in a single PL/SQL procedure was a smart move, and saved 99% of the time that would be needed for a really naive, row-by-row-from-the-client solution.

share|improve this answer

Possibly good enough, but if the external source of the keys is a file, create an external table pointing at the file, to expose the data in the file as a relational table and then you can potentially do it in a merge (update) statement.

share|improve this answer

Your Answer


