UPDATE schema.names set last_name = (
SELECT *
FROM (
SELECT last_name
FROM schema.names
ORDER BY DBMS_RANDOM.RANDOM))
这是一个用于混乱名称的查询语句：
select n.*, n2.name as new_name
from (select n.*, row_number() over (order by dbms_random.random) as seqnum
from schema.names n
) n join
(select n.*, row_number() over (order by dbms_random.random) as seqnum
from schema.names n
) n2
on n.seqnum = n2.seqnum;
merge中，假设您有一个主键：merge into schema.names n
using (select n.*, n2.name as new_name
from (select n.*, row_number() over (order by dbms_random.random) as seqnum
from schema.names n
) n join
(select n.*, row_number() over (order by dbms_random.random) as seqnum
from schema.names n
) n2
on n.seqnum = n2.seqnum
) nn
on n.? = nn.?
when matched then update
set n.name = nn.new_name;
修改：以下的PL/SQL块使用Gordon答案中的查询来驱动循环。
设置：
create table demo
( name_original varchar2(10)
, name_new varchar2(10) );
-- Two columns initially the same so we can check the results:
insert into demo
select column_value, column_value
from table(sys.dbms_debug_vc2coll('Jim','James','Joe','Jenny','Jane','Jacky'));
代码：
begin
for r in (
select n.rowid as rwd
, n.name_original
, n2.name_new as name_shuffled
from ( select n.*, row_number() over(order by dbms_random.value) as seqnum
from demo n ) n
join
( select n.*, row_number() over(order by dbms_random.value) as seqnum
from demo n ) n2
on n.seqnum = n2.seqnum
)
loop
update demo set name_new = r.name_shuffled
where rowid = r.rwd;
end loop;
end;
以下是我的原始答案，对OP没有特别的帮助，但我认为它在技术上很有趣：
优化器意识到一个不相关的单行子查询只需要执行一次，因此您可以获得一个值并将其应用于所有行。可能有一个提示可以防止这种情况，但我在快速查看中找不到一个（no_merge，no_unnest和rule没有效果）。
以下方法适用于Oracle 12.1，但只是通过强制规定旧名称和新名称必须不同来实现的，使其成为一个相关子查询，优化器必须为每一行评估它。（它还会生成重复项，因为每个随机查找都是独立的，所以可能对您没有用。）
update demo d set name_new =
( select name_new
from demo d2
where d2.name_new <> d.name_new
order by dbms_random.random
fetch first row only );
select * from demo;
NAME_ORIGINAL NAME_NEW
------------- ----------
Jim Jenny
James Jane
Joe Jacky
Jenny Jane
Jane Jacky
Jacky Jim
where d2.name_new <> d.name_new如何改变执行计划，如下所示：Plan hash value: 1813657616
-----------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers |
-----------------------------------------------------------------------------------------------
| 0 | UPDATE STATEMENT | | 1 | | 0 |00:00:00.01 | 27 |
| 1 | UPDATE | DEMO | 1 | | 0 |00:00:00.01 | 27 |
| 2 | TABLE ACCESS STORAGE FULL | DEMO | 1 | 82 | 6 |00:00:00.01 | 7 |
|* 3 | VIEW | | 1 | 1 | 1 |00:00:00.01 | 7 |
|* 4 | WINDOW SORT PUSHED RANK | | 1 | 82 | 1 |00:00:00.01 | 7 |
| 5 | TABLE ACCESS STORAGE FULL| DEMO | 1 | 82 | 6 |00:00:00.01 | 7 |
-----------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - filter("from$_subquery$_003"."rowlimit_$$_rownumber"<=1)
4 - filter(ROW_NUMBER() OVER ( ORDER BY "DBMS_RANDOM"."RANDOM"())<=1)
转换为：
Plan hash value: 1813657616
--------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | OMem | 1Mem | Used-Mem |
--------------------------------------------------------------------------------------------------------------------------
| 0 | UPDATE STATEMENT | | 1 | | 0 |00:00:00.01 | 62 | | | |
| 1 | UPDATE | DEMO | 1 | | 0 |00:00:00.01 | 62 | | | |
| 2 | TABLE ACCESS STORAGE FULL | DEMO | 1 | 82 | 6 |00:00:00.01 | 7 | 1025K| 1025K| |
|* 3 | VIEW | | 6 | 1 | 6 |00:00:00.01 | 42 | | | |
|* 4 | WINDOW SORT PUSHED RANK | | 6 | 4 | 6 |00:00:00.01 | 42 | 2048 | 2048 | 2048 (0)|
|* 5 | TABLE ACCESS STORAGE FULL| DEMO | 6 | 4 | 30 |00:00:00.01 | 42 | 1025K| 1025K| |
--------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - filter("from$_subquery$_003"."rowlimit_$$_rownumber"<=1)
4 - filter(ROW_NUMBER() OVER ( ORDER BY "DBMS_RANDOM"."RANDOM"())<=1)
5 - filter("D2"."NAME_NEW"<>:B1)
这是一个很好的例子，展示了相同的执行计划（计划哈希值为1813657616）在两个不同的情况下执行着截然不同的操作，如果你需要其中之一，可以参考这个例子。
（如果有一个提示可以做到同样的事情，那么它将成为一个很好的例子，说明提示如何改变结果。）
where rownum = 1添加到外部的SELECT。 - Gordon Linoff