Best practice to update multiple rows based on key list

Question

I'm curious what is the best practise to update multiple rows in my database if I have a list of ID's provided by an external system?

Simple UPDATE table SET y=z WHERE id IN (?); is not the way as we can get more than tousand IDs, which is Oracles limit.

Of course we have some ideas, like splitting into multiple queries, by using temporary table and EXIST condition but Id'like to see the best practise and know the theory behind.

EDIT: this question is not a race for the fastest answer, but about the best practice and its theoretical background. IDs are provided by an external system! No other table for join nor other select. By temporary table I meant global temporary table (oracle feature), not creating anf dropping new table for each query! Please do not answer just because you want to be fast or if you do not get the point.

Possible duplicate of [How to resolve ORA-01795 in Java code](http://stackoverflow.com/q/26745971/266304). An array would let your update become `UPDATE table SET y=z WHERE id IN (SELECT column_value FROM TABLE(?))`. — Alex Poole, Nov 12 '15 at 15:37
How often does this query run? Some typical best practices may not apply if this query is only run a few times a day. — Jon Heller, Nov 12 '15 at 17:43
There may not be *a* best practice anyway; there are several approaches, and they may trade off simplicity, ease of coding/maintenance, speed, load, ... I'd use the array/table collection method but there may be advantages to others I haven't though about, or even heard about; and I haven't benchmarked the options. That one has been more than good enough for me, but might not be right for you. — Alex Poole, Nov 12 '15 at 18:23
There are 10-50 devices and the query runs twice per minute for each device, could be more or less often, depends on the configuration. — Kousalik, Nov 13 '15 at 06:41

score 0 · Answer 1 · answered Nov 12 '15 at 15:26

0

Can you use this?

UPDATE table
SET a.column=b.column
FROM table a
JOIN your_id_table b ON a.id=b.id

How are you filling in the "(?)" in your IN clause?

answered Nov 12 '15 at 15:26

A. Greensmith

355
1
8

The question says it's "a list of ID's provided by an external system", not from another table; so `your_id_table` doesn't exist? Are you proposing a temporary table populated somehow, or a table collection expression, or assuming there is a real table to join to? – Alex Poole Nov 12 '15 at 15:42
As proposed temporary table I meant Global Temporary Table (Oracle feature). I would insert IDs I want to update and then go for update using condition WHERE EXISTS. – Kousalik Nov 13 '15 at 06:43
Alex, I asked for clarity how he fills in the blank in his IN clause because this would point to how he is currently storing the data. It could have been a table variable, temporary table or permanent table as Kousalik describes. – A. Greensmith Nov 17 '15 at 15:39

score 0 · Answer 2 · answered Nov 12 '15 at 15:28

0

You probably have the list in another table so

UPDATE table 
SET y=z 
WHERE id IN (SELECT ID 
             FROM YourOtherTable);

Also can use exists

UPDATE table T1
SET y=z 
WHERE Exists (SELECT ID 
              FROM YourOtherTable T2
              WHERE T2.ID = T1.ID);

answered Nov 12 '15 at 15:28

Juan Carlos Oropeza

47,252
12
78
118

No, i do not have the list in another table. – Kousalik Nov 12 '15 at 15:40
Be carefull with using IN (subquery) as this is a performance killer. The subquery has to be completely evaluated which can take a while. EXISTS alternative is in such case better and usualy faster way and is also the option I would use together with the temporary table for storing IDs. – Kousalik Nov 13 '15 at 06:50

score 0 · Answer 3 · answered Nov 12 '15 at 15:37

Edit: I assumed you are using Java to do your updates.

Doesn't it take longer to create a temporary table, fill it with your IDs and then execute against it? (Or maybe I am misreading the scenario).

What I'd do is split the big list of IDs into partitions and then use an executor to submit the batches to my DB layer, e.g.:

            ExecutorService newFixedThreadPool = Executors.newFixedThreadPool(10);
            List<Integer> aLotOfIds = new ArrayList<Integer>(); // imagine heaps of IDS
            List<List<Integer>> partition = Lists.partition(aLotOfIds, 1000);
            partition.forEach( p -> {
                newFixedThreadPool.submit(() -> {
                    // execute update for a batch
                });
            });

Global Temporary Table is Oracle feature. Does not mean creating and droping table each time the query runs. — Kousalik, Nov 13 '15 at 06:47

Best practice to update multiple rows based on key list

3 Answers3