使用SUM函数跨表更新balance触发非空约束错误的解决咨询

问题原因 #

当子查询select sum(open_amount_total) from fnt_pym_profile where fnt_pym_profile.profile_id =apt_profile.id and status ='OPEN'没有找到符合条件的记录时，SUM函数会返回NULL，而apt_profile.balance字段设置了非空约束，因此触发null value in column "balance" violates not-null constraint错误。

解决方案 #

方案1：将NULL替换为默认非空值（推荐）

使用COALESCE函数把子查询返回的NULL替换成你需要的默认值（比如0），确保写入balance的永远是非空值：

update apt_profile 
set payment_currency_code ='CC',
balance = COALESCE(
    (select sum(open_amount_total) from fnt_pym_profile where fnt_pym_profile.profile_id =apt_profile.id and status ='OPEN'),
    0
)
where payment_currency_code ='DD';

方案2：仅更新存在匹配记录的行

如果不想给无匹配记录的行设置默认值，而是直接跳过这些行，可以用EXISTS子句过滤：

update apt_profile 
set payment_currency_code ='CC',
balance =  (select sum(open_amount_total) from fnt_pym_profile where fnt_pym_profile.profile_id =apt_profile.id and status ='OPEN')
where payment_currency_code ='DD'
and exists (
    select 1 from fnt_pym_profile where fnt_pym_profile.profile_id =apt_profile.id and status ='OPEN'
);

内容的提问来源于stack exchange，提问作者None