Update和group by不能一起用时，使用子查询替代

-- 先找出符合条件且doc_no重复的记录

WITH duplicates AS (

    SELECT doc_no

    FROM tb_cdrgt_pers_spm_info

    WHERE prov_code = '44' 

        AND doc_kind_no = '180005' 

        AND tx_inst_no IN ('4407854', '44023185')

    GROUP BY doc_no

    HAVING COUNT(*) > 1

)

 

-- 然后更新这些记录的状态

UPDATE tb_cdrgt_pers_spm_info

SET record_sta_cd = '0'

WHERE prov_code = '44' 

    AND doc_kind_no = '180005' 

    AND tx_inst_no IN ('4407854', '44023185')

    AND doc_no IN (SELECT doc_no FROM duplicates);

 

COMMIT;