sql - 使用子查询的 Oracle 多行更新

Question

我正在尝试“洗牌”名称表中的last_names 值。我想使用子选择查询来随机化名称的顺序并相应地更新它们。我出于混淆原因想要这样做，但希望它仍然看起来像一个真实的数据集。

下面的语句返回“ORA-01427:单行子查询返回多于一行”

我怎样才能做到这一点？

UPDATE schema.names set last_name = (
      SELECT * 
        FROM (
         SELECT last_name
         FROM schema.names
         ORDER BY DBMS_RANDOM.RANDOM))

Answer 1

这是一个打乱名称的查询:

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