Oracle Multi Row Update using Subquery

Question

I am trying to "shuffle" last_names values in the names table. I am wanting to use a sub-select query that randomizes the order of the names and updates them accordingly. I wanting to do this for obfuscation reasons but want it to still look like a real data set.

The statement below returns a "ORA-01427: single-row subquery returns more than one row"

How can I make this work?

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

Answer 1

Here is a query that shuffles the names:

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;

You can incorporate this into a merge, assuming you have a primary key:

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;