我的批评仅限于您的表设计未规范化，也不是很漂亮的情况，但我假设您有自己的理由.我通常通过将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>;