我正在尝试“洗牌”名称表中的last_names 值。我想使用子选择查询来随机化名称的顺序并相应地更新它们。我想这样做是出于混淆原因,但希望它仍然看起来像一个真实的数据集。
下面的语句返回“ORA-01427:单行子查询返回多于一行”
我怎样才能做到这一点?
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;
本文内容由网友自发贡献,版权归原作者所有,本站不承担相应法律责任。如您发现有涉嫌抄袭侵权的内容,请联系:hwhale#tublm.com(使用前将#替换为@)