Updates to multiple columns with same criteria

[dpatfield66]

If I have multiple fields in a record (row) that are NULL, let's say, and I want to update them to be 0 instead, how would I do this? Does anyone know?

I don't want to use multiple update queries, just one.
ONE query that says, hey...if the field entry is NULL, make it 0.

Or if the Date Field is 1/1/1900, make it NULL.

So, if I have 5 Null fields...
or 5 Date fields with 1/1/1900...

I want to update each field above to whatever (0 in the first case, Null in the second)

These don't have to be the same query...I can have one query for the NULL to 0 update, and one query for the 1/1/1900 to NULL update.

But I don't want 5 queries for each NULL field
Or 5 different queries for each date with 1/1/1900

I KNOW this can be done, anyone???

 

[fpineda101]

In Oracle, when you create the table, you can set the default value of a column if it is null. I imagine you can do the same thing in SQL Server.

You can also create a function that returns 0 if the value is null.

Something like this...

INSERT INTO my_table (my_col1, my_col2, my_col3)
VALUES (my_val1, my_val2, Make0ifNull(my_val3));

This assumes you created a function called Make0ifNull in your database

 

[dpatfield66]

Thanks for the suggestions:

I actually found what I needed:

There is a case statement in the update query that I use.

Update myTable
SET
date1 = case date1 = '1/1/1900' then NULL Else date1 end case,
date1 = case date2 = '1/1/1900' then NULL Else date2 end case,
etc...