使用MERGE语句：

MERGE INTO table_name dst
USING (
  SELECT ROWID as rid,
         MAX(CASE active_code WHEN 'Y' THEN empid END)
           OVER (PARTITION BY uniq_id) AS new_empid
  FROM   table_name
) src
ON (src.rid = dst.ROWID AND dst.active_code = 'N')
WHEN MATCHED THEN
  UPDATE
  SET  empid = new_empid;

或者相关的UPDATE：

UPDATE table_name dst
SET empid = ( SELECT empid
              FROM   table_name src
              WHERE  src.uniq_id = dst.uniq_id
              AND    src.active_code = 'Y'
              AND    ROWNUM = 1 )
WHERE active_code = 'N';

其中，对于样本数据：

CREATE TABLE table_name (empid, active_code, uniq_id) AS
SELECT 1,   'Y', 123 FROM DUAL UNION ALL
SELECT 100, 'N', 123 FROM DUAL UNION ALL
SELECT 100, 'N', 123 FROM DUAL UNION ALL
SELECT 100, 'N', 123 FROM DUAL UNION ALL
SELECT 100, 'N', 123 FROM DUAL UNION ALL
SELECT 2,   'Y', 456 FROM DUAL UNION ALL
SELECT 200, 'N', 456 FROM DUAL UNION ALL
SELECT 200, 'N', 456 FROM DUAL UNION ALL
SELECT 200, 'N', 456 FROM DUAL UNION ALL
SELECT 200, 'N', 456 FROM DUAL;

然后，在MERGE或UPDATE之后，该表包含：
| 活动代码|UNIQ_ID| UNIQ_ID |
| - -----|- -----| ------------ |
| Y轴|一百二十三| 123 |
| 不适用|一百二十三| 123 |
| 不适用|一百二十三| 123 |
| 不适用|一百二十三| 123 |
| 不适用|一百二十三| 123 |
| Y轴|四百五十六| 456 |
| 不适用|四百五十六| 456 |
| 不适用|四百五十六| 456 |
| 不适用|四百五十六| 456 |
| 不适用|四百五十六| 456 |
fiddle