null value in column "balance" violates not-null constraint-CodePudding

I want to update balance from another table, with sum function but im getting this error:

null value in column "balance" violates not-null constraint

Any suggestion how can i check to not update null values or to change query so that works?

This is my query:

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';

CodePudding user response：

Use COALESCE.

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';

This should 0 the balance.

CodePudding user response：

OR use ISNULL:

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';