Oracle 21c

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
ALTER SESSION SET NLS_DATE_FORMAT = 'YYYY-MM-DD HH24:MI:SS';
CREATE TABLE Table_A (ID, NAME) AS
SELECT 1, 'NAME1' FROM DUAL UNION ALL
SELECT 2, 'NAME1' FROM DUAL UNION ALL
SELECT 3, 'NAME2' FROM DUAL UNION ALL
SELECT 4, 'NAME2' FROM DUAL;
4 rows affected

CREATE TABLE Table_B (ID, NAME) AS
SELECT CAST(NULL AS NUMBER), 'NAME1' FROM DUAL UNION ALL
SELECT NULL, 'NAME1' FROM DUAL UNION ALL
SELECT NULL, 'NAME2' FROM DUAL UNION ALL
SELECT NULL, 'NAME2' FROM DUAL;
4 rows affected

MERGE INTO table_b dst
USING (
  SELECT a.id,
         b.rid
  FROM   (
           SELECT id,
                  name,
                  ROW_NUMBER() OVER (PARTITION BY name ORDER BY id, ROWNUM) AS rn
           FROM   table_a
         ) a
         INNER JOIN (
           SELECT ROWID as rid,
                  name,
                  ROW_NUMBER() OVER (PARTITION BY name ORDER BY id, ROWNUM) AS rn
           FROM   table_b
         ) b
         ON a.name = b.name AND a.rn = b.rn
) src
ON (src.rid = dst.ROWID)
WHEN MATCHED THEN
  UPDATE
  SET id = src.id;
4 rows affected

SELECT * FROM table_b;


ID
NAME


1
NAME1

2
NAME1

3
NAME2

4
NAME2