Oracle 10g PL/SQL-选择结果作为更新列值 [英] Oracle 10g PL/SQL- Select results as update column values

问题描述

是否可以使用Oracle 10g中的查询的记录值轻松/优雅地更新表中的多列?

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

为了简单起见，我有一个表(称为Source)，其中有2列ID和KEY.

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
...

我还有另一个表(称为KeyMap)，该表采用trunc(ID/100)并将其用作batchID 这些列是批次中ID的键映射:

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               

ID是分批创建和处理的，因此在批处理结束时，我想调用一个存储过程，以使用新的Key值和1条update语句使用新的Key值更新KeyMap表中的记录，使用提供这些键值的子选择或集合.

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?

推荐答案

我的批评仅限于您的表设计未规范化，也不是很漂亮的情况，但我假设您有自己的理由.我通常通过将DECODE与一个聚合列结合使用来进行这些轮换"查询，并按我的密钥进行分组-在这种情况下，您的伪密钥为trunc(ID/100).结合使用元组的更新语法:

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

您会得到:

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

这篇关于Oracle 10g PL/SQL-选择结果作为更新列值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！