UPDATE QUERY

[sbaud2003]

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.

 

[Isaac]

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)

 

[HalloweenWeed]

You need to run a separate query for each field to do that that way.

 

[Gasman]

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.?

 

[sbaud2003]

Just to explain, if one of thosee is Null you still set ALL the others to zero.?

oh! then how to update if individual filed on Null

 

[Isaac]

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.

 

[Isaac]

The way I took it, he wants to update each field to, if it's Null, 0.

 

[Minty]

@pisorsisaac@gmail.co - That should work but if it's a big data set, I suspect it will be painfully slow.

 

[Isaac]

@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

 

[Gasman]

pisorisaac has the solution

UPDATE Table1 SET Table1.Field2 = IIf(IsNull([Field2]),0,[Field2])
WHERE (((Table1.Field2) Is Null));