Oracle 10g PL/SQL- Select results as update column values

Question

Is there a way to easily/elegantly update multiple columns in a table with record values from a query in Oracle 10g?

I have a table (call it Source) which has for simplicities sake, 2 columns, ID and KEY.

ID     Key
----   ---- 
1        1000
2        1000
3        5000
4        1000
..
101      8000
102      9000
103      7000
104      9000
...
201         5
202         5
...

I have another table (call it KeyMap) that takes trunc(ID/100) and uses it as a batchID with the columns being a key map for the IDs within the batch:

trunc(ID/100)   key1   key2   key3   key4 ..... key99
-------------   ----   ----   ----   ----
0               1000   1000   5000   1000
1               8000   9000   7000   9000
2                  5      5               

The ID's are created and processed in batches, so at the conclusion of the batch processing I would like to call a stored procedure to update the record in the KeyMap table with the new Key values with 1 update statement using a sub-select or collection providing those key values. Is this possible and what is the best/most efficient way of doing this?

Thanks in advance.

Answer 1

you could generate an Oracle VARRAY and pass in your keys in a batch. your procedure could iterate over the VARRAY and update the table.

Answer 2

I'll limit my criticism to say that your table design is not normalized, and isn't very pretty, but I'll assume you have your reasons. I typically do these "rotation" queries by using DECODE combined with a aggregate column, grouping by my key - in this case, your pseudo-key, trunc(ID/100). Combine that with the update syntax that uses tuples:

 UPDATE Foo
    SET (a, b, c, d)
      = (w, x, y, z);

and you get:

  UPDATE KeyMap
     SET
       ( key1
       , key2
       , key3
       , key4
       ...
       , key99
       )
       = ( SELECT MAX(decode(mod(ID, 100), 1, Key, NULL))
                , MAX(decode(mod(ID, 100), 2, Key, NULL))
                , MAX(decode(mod(ID, 100), 3, Key, NULL))
                , MAX(decode(mod(ID, 100), 4, Key, NULL))
                ...
                , MAX(decode(mod(ID, 100), 99, Key, NULL))
             FROM Source
            WHERE Trunc(Source.ID / 100) = KeyMap.batchId
            GROUP BY Trunc(Source.ID / 100)
         )
   WHERE BatchId = <x>;