在行之间随机排列一列

Question

如何有效地洗净大型（1m至5m记录）表的内容？已知该列具有唯一值，但是您可以假定为此删除了所有约束。我之所以头痛，主要是因为我正在更新选择的同一列。我的目标是使用PL
/ SQL做到这一点，以便我可以以编程方式采取其他措施，例如记录或更新其他表。

**Original table:**
+----+-----------+
| id | fname     |
+----+-----------+
|  1 | mike      |
|  2 | ricky     |
|  3 | jane      |
|  4 | august    |
|  6 | dave      |
|  9 | J茅r么me    |
+----+-----------+

**Possible output:**
+----+-----------+
| id | fname     |
+----+-----------+
|  1 | dave      |
|  2 | jane      |
|  3 | mike      |
|  4 | ricky     |
|  6 | J茅r么me    |
|  9 | august    |
+----+-----------+

我最近的尝试是创建一个使用的游标，over (order by dbms_random.value)并尝试基于行数进行合并或更新。也许我可以通过创建一个临时表来解决修改自我约束的问题？我相当有信心Oracle可以采用一些特殊的方法来做到这一点，但是我的SQL能力仅限于基本的CRUD命令。

完整的解决方案在这里，基于戈登的答案：

merge into t
using (
select t.id, t2.name
from (select t.*, rownum as seqnum
      from t
     ) t join
     (select t.*, row_number() over (order by dbms_random.value) as seqnum
      from t
     ) t2
     on t.seqnum = t2.seqnum
) src
on (t.id = src.id)
when matched then update set t.name = src.name;

Answer 1

您可以使用随机行号进行自我连接：

select t.id, t2.name
from (select t.*, row_number() over (order by dbms_random.value) as seqnum
      from t
     ) t join
     (select t.*, row_number() over (order by dbms_random.value) as seqnum
      from t
     ) t2
     on t.seqnum = t2.seqnum;

实际上，您不需要将两者都随机化：

select t.id, t2.name
from (select t.*, rownum as seqnum
      from t
     ) t join
     (select t.*, row_number() over (order by dbms_random.value) as seqnum
      from t
     ) t2
     on t.seqnum = t2.seqnum;