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

userVCVi 0
Viewed 40

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';
sql postgresql
2 Answers

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.

Joop Eggen0

OR use ISNULL:

update apt_profile
set payment_currency_code ='CC',
balance =  ISNULL(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';
Stas0
Related