Oracle 10g PL/SQL-选择结果作为更新列值
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/1003011/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Oracle 10g PL/SQL- Select results as update column values
提问by RC.
Is there a way to easily/elegantly update multiple columns in a table with record values from a query in Oracle 10g?
有没有办法使用 Oracle 10g 中查询的记录值轻松/优雅地更新表中的多个列？
I have a table (call it Source) which has for simplicities sake, 2 columns, IDand KEY.
我有一个表（称为 Source），为了简单起见，它有 2 列 ID和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 batchIDwith the columns being a key map for the IDs within the batch:
我有另一个表（称之为KeyMap），它采用trunc(ID/100)并将其用作 a batchID，其中列是批处理中 ID 的键映射：
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 KeyMaptable with the new Keyvalues with 1 update statement using a sub-select or collection providing those key values.
ID 是批量创建和处理的，因此在批处理结束时，我想调用一个存储过程，使用提供这些键的子选择或集合，使用 1 个更新语句用KeyMap新Key值更新表中的记录值。
Is this possible and what is the best/most efficient way of doing this?
这可能吗？最好/最有效的方法是什么？
回答by Steve Broberg
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:
我会限制我的批评说你的桌子设计没有规范化，不是很漂亮，但我假设你有你的理由。我通常使用 DECODE 和聚合列组合来执行这些“旋转”查询，按我的键分组 - 在这种情况下，是您的伪键 trunc(ID/100)。将其与使用元组的更新语法结合起来：
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>;
