UPDATE QUERY

sbaud2003

Member
Local time
Tomorrow, 04:19
Joined
Apr 5, 2020
Messages
186
Hi everyone,
I have a query name Qnull which suppose to put values in the filed as Zero if null
the SQL is

UPDATE sbicmp SET sbicmp.CGHS = 0, sbicmp.IT = 0, sbicmp.CGIES = 0, sbicmp.GST = 0, sbicmp.RENT = 0, sbicmp.MISC = 0
WHERE (((sbicmp.CGHS) Is Null)) OR (((sbicmp.IT) Is Null)) OR (((sbicmp.CGIES) Is Null)) OR (((sbicmp.GST) Is Null)) OR (((sbicmp.RENT) Is Null)) OR (((sbicmp.MISC) Is Null));
sbicmp.MISC is the new file i have added after that
whenever i fired the query all the fields value becomes zero respective of having values other than null

any soloution.
 
Last edited:
I think what you want to be doing is look into using the IIF() function inside your SET statement ...

(aircode)

Update tablename set table.column = iif(table.column is null, 0,table.column)
 
You need to run a separate query for each field to do that that way.
 
Hi everyone,
I have a query name Qnull which suppose to put values in the filed as Zero if null
the SQL is

UPDATE sbicmp SET sbicmp.CGHS = 0, sbicmp.IT = 0, sbicmp.CGIES = 0, sbicmp.GST = 0, sbicmp.RENT = 0, sbicmp.MISC = 0
WHERE (((sbicmp.CGHS) Is Null)) OR (((sbicmp.IT) Is Null)) OR (((sbicmp.CGIES) Is Null)) OR (((sbicmp.GST) Is Null)) OR (((sbicmp.RENT) Is Null)) OR (((sbicmp.MISC) Is Null));
sbicmp.MISC is the new file i have added after that
whenever i fired the query all the fields value becomes zero respective of having values other than null

any soloution.
Just to explain, if one of those is Null you still set ALL the others to zero.?
 
Last edited:
You need to run a separate query for each field to do that that way.
Not to do it the way I suggested. My suggestion, if used on all fields in the same query, will set the column to 0 if it's null, else leave it how it is.
 
The way I took it, he wants to update each field to, if it's Null, 0.
 
@Minty - well, but if he leaves in the Where clause, at least that will help. He can still leave in the where clause requiring at least one of those columns to be null
 
pisorisaac has the solution
Code:
UPDATE Table1 SET Table1.Field2 = IIf(IsNull([Field2]),0,[Field2])
WHERE (((Table1.Field2) Is Null));
 

Users who are viewing this thread

Back
Top Bottom