Oracle Shuffle columns in rows

Question

Based in the solution if this issue - Shuffle a column between rows - I need to apply to this, a new condition: the names must guarantee the gender: male, female or unknown.

So, my table have a column named gender. I need to shuffle the columns whit the same gender.

I've tried this, but in some cases I cannot guarantee the gender

merge into original_table o
  using (
    with
         helper ( id, gender, rn, rand_rn ) as (
           select id,
                  gender,
                  row_number() over (order by id),
                  row_number() over (order by dbms_random.value())
           from   original_table
         )
    select ot.name, ot.gender, h2.id
    from   original_table ot inner join helper h1 on (ot.id = h1.id and ot.gender = h1.gender)
                             inner join helper h2 on h1.rand_rn = h2.rn
  ) p
on (o.id = p.id)
when matched then update set o.name = p.name
;

And in cases that gender is not available (is null value), for example company's name, this merge not update anything.

I can split this query in 3 different merge statement. One for each gender that I have. But I'm looking for a better and simply statement. Because I need to apply the same solution in a different context and with different conditions. Thanks.

EDIT: Sample data

 ID       GENDER  NAME                                                                                                 
3721       M      MARK
3722       M      JUSTIN
3723       F      RUTH
3724       F      MARY
3725       F      ANNE
4639              CAMPANY SA                                                                               
4640       M      JOHN
4641       M      LUCAS
4642              COMPANY HOLDER SA 

One possible solution:

 ID       GENDER  NAME                                                                                                 
3721       M      LUCAS
3722       M      JOHN
3723       F      MARY
3724       F      ANNE
3725       F      RUTH
4639              CAMPANY HOLDER SA                                                                               
4640       M      MARK
4641       M      JUSTIN
4642              COMPANY SA 

Answer 1

Include gender in the PARTITION BY clause in the analytic functions and in the JOIN clause. If you don't have a primary key to match on then you could use the ROWID pseudo-column.

Oracle Setup:

CREATE TABLE original_table ( id, gender, name, company ) AS
SELECT 1, 'F', 'Alice', CAST( NULL AS VARCHAR2(20) ) FROM DUAL UNION ALL
SELECT 2, 'M', 'Bobby', 'ACME' FROM DUAL UNION ALL
SELECT 3, 'F', 'Carol', 'XYZ'  FROM DUAL UNION ALL
SELECT 4, 'M', 'David', NULL   FROM DUAL UNION ALL
SELECT 5, 'M', 'Errol', 'ACME' FROM DUAL UNION ALL
SELECT 6, 'F', 'Fiona', 'XYZ'  FROM DUAL;

Update:

MERGE INTO original_table dst
USING (
  WITH rnd ( rid, rn, rnd_rn, gender, name ) AS (
    SELECT ROWID,
           ROW_NUMBER() OVER ( PARTITION BY gender ORDER BY id ),
           ROW_NUMBER() OVER ( PARTITION BY gender ORDER BY DBMS_RANDOM.VALUE ),
           gender,
           name
    FROM   original_table
  )
  SELECT o.rid, r.name
  FROM   rnd o INNER JOIN rnd r
         ON ( ( o.gender = r.gender OR ( o.gender IS NULL AND r.gender IS NULL ) )
             AND o.rn = r.rnd_rn )
)
ON ( dst.ROWID = src.ROWID )
WHEN MATCHED THEN
  UPDATE SET name = src.name;

Output:

SELECT * FROM original_table;

ID G NAME  COMPANY
-- - ----- -------
 1 F Fiona  
 2 M David ACME
 3 F Alice XYZ
 4 M Bobby 
 5 M Errol ACME
 6 F Carol XYZ