RC. RC. - 16 days ago 5
SQL Question

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

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?

Answer

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>;
Comments