using子句中的src子查询只为每个accountno返回1个随机行。您需要聚合它们，例如使用pivot:

with source(accountno, cell, site, contact) as ( --test data:
   select 1,8881234567,8881235678,8881236789 from dual union all
   select 1,8881234567,8881235678,8881236789 from dual
)
select accountno, contact, 
       r1_cell, r1_site, 
       r2_cell, r2_site
from (select s.*,row_number()over(partition by accountno order by cell) rn
      from source s
     )
pivot (
   max(cell) cell,max(site) site
    FOR rn
    IN (1 R1,2 R2)
)

最后，您可以将r1\u cell、r1\u site、r2\u cell、r2\u site与目标值进行比较，并使用所需的值：

MERGE INTO target tgt
    USING (
            select accountno, contact, 
                   r1_cell, r1_site, 
                   r2_cell, r2_site
            from (select s.*,row_number()over(partition by accountno order by cell) rn
                  from source s
                 )
            pivot (
               max(cell) cell,max(site) site
                FOR rn
                IN (1 R1,2 R2)
            )
          ) src 
    ON (tgt.accountno = src.accountno)
WHEN MATCHED
    THEN 
        UPDATE SET
                  tgt.phone4 =  
                  CASE 
                       WHEN src.r1_cell <> tgt.cell 
                          THEN src.r1_cell
                          ELSE src.r2_cell
                  END,
                  tgt.phone5 =
                  CASE WHEN src.r1_site <> tgt.site 
                     THEN src.r1_site
                     ELSE src.r2_site
                  END
/

问题在于您在第\行中使用的逻辑，即用相同的帐号对行进行编号。

MERGE 
 INTO target tgt
USING (select accountno, cell, site, contact, email1 
         from (select w.accountno, w.cell, w.site, w.contact, email1
                    , row_number() over (PARTITION BY w.accountno order by w.accountno desc) acct 
                from source w
              left join target w2
                  on w.accountno=w2.accountno
               where w2.cell is null /* get records which are not in target*/ 
              ) inn 
         where inn.acct =1
       ) src 
   ON (tgt.accountno = src.accountno)
WHEN MATCHED THEN 
     UPDATE 
        SET tgt.phone4 = src.cell,
            tgt.phone5 = src.site