这是一个通用方法：

update t
    set primary_flag = (case when t.id = (select min(t2.id)
                                          from t t2
                                          where t2.account_number = t.account_number
                                         )
                             then 1 else 0
                        end)
    where t.account_number in (select t2.acount_number
                               from t t2
                               group by t2.acount_number
                               having sum(t2.primary_flag) <> 1
                              );

这是标准SQL，适用于大多数数据库。但是，特定的数据库可能具有更简洁或更有效的方法来完成相同的工作。

将表加入到查询中，该查询返回需要更新的每个account_number的最小table_id：

update t 
set t.primary_flag = case when t.table_id = g.minid then 1 else 0 end
from tablename t inner join (
  select account_number,min(table_id) minid
  from tablename
  group by account_number
  having sum(primary_flag) <> 1
) g on g.account_number = t.account_number;

请参见demo。

或使用CTE：

with cte as (
  select *,row_number() over (partition by account_number order by table_id) rn,sum(primary_flag) over (partition by account_number) total
  from tablename  
)
update cte 
set primary_flag = case when rn = 1 then 1 else 0 end
where total <> 1

请参见demo。
为了避免对不需要更新的行进行不必要的更新：

with cte as (
  select *,sum(primary_flag) over (partition by account_number) total
  from tablename  
)
update cte 
set primary_flag = abs(primary_flag - 1)
where (total <> 1) and ((rn = 1 and primary_flag = 0) or (rn > 1 and primary_flag = 1))

请参见demo。

结果：

> TABLE_ID | ACCOUNT_NUMBER | PRIMARY_FLAG
> -------: | :------------- | -----------:
>        1 | ABC123         |            0
>        2 | ABC123         |            1
>        3 | ABC123         |            0
>        4 | 987XYZ         |            1
>        5 | 987XYZ         |            0
>        6 | 987XYZ         |            0
>        7 | 5A5B5C         |            1
>        8 | 5A5B5C         |            0
>        9 | 5A5B5C         |            0
>       10 | 5A5B5C         |            0