CODEWITHSUNDEEP
mysqlsqldatabase
Azeem112
Azeem112

Reputation: 377

How to update multiple rows of multiple columns with single value having same condition for every column

I have a table having different type of Fees like security fees, tuition fees, admission fees etc and all of these column accept null values too.

I want to update all null values of these columns with 0 value. I can write different update query for each column but because condition and value for all the columns is same I wanna learn to do it in a single update query.

Table_Fees
securityFees | TuitionFees | AdmissionFees
 150            500             null
 null           450              120
 150            null             120

How can I replace all null values with zero?

Update Table_Fees
 set securityFees = 0,
     TuitionFees  = 0,
     AdmissionFees = 0
where securityFees = null 
and   TuitionFees  = null
and   AdmissionFees = null;

or/and both operator are not useful in this case

PS: I can't change the structure of table. I can only replace null values with 0 through coding.

Upvotes: 0

Views: 665

Answers (4)

Алексей Мокрев
Алексей Мокрев

Reputation: 192

if you use MySQL

Update Table_Fees
 set securityFees = IFNULL(securityFees,0),
     TuitionFees  = IFNULL(TuitionFees,0),
     AdmissionFees = IFNULL(AdmissionFees,0)
where securityFees is null 
or   TuitionFees  is null
or   AdmissionFees is null;

Upvotes: 3

Azeem112
Azeem112

Reputation: 377

I've solved the answer by CASE 

Update Table_Fees
 set securityFees = CASE when securityFees = null then 0 else securityFees END,
     TuitionFees  = CASE when TuitionFees  = null then 0 else TuitionFees  END,
     AdmissionFees = CASE when AdmissionFees = null then 0 else AdmissionFees END;

Upvotes: 0

Rohit shah
Rohit shah

Reputation: 819

Update Table_Fees
set securityFees = IFNULL(securityFees,0),
    TuitionFees  = IFNULL(TuitionFees,0),
    AdmissionFees = IFNULL(AdmissionFees,0)

// 3 individual querys

Update Table_Fees
set securityFees = 0
where securityFees is null;

Update Table_Fees
set TuitionFees = 0
where TuitionFees is null;

Update Table_Fees
set AdmissionFees = 0
where AdmissionFees is null;

No need for where condition if it has to updated in entire table.

Hope this works for you.

Let me know if any problem

Upvotes: 1

G.Arima
G.Arima

Reputation: 1171

Run this and you will get output scrips as required.

SELECT 'UPDATE yourtable SET ' + name + ' = NULL WHERE ' + name + ' = '''';'
FROM syscolumns
WHERE id = object_id('yourtable')
  AND isnullable = 1;

Upvotes: 0

Related Questions