Updating multiple columns with same criteria

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Updating multiple columns with same criteria

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2006-05-15 : 08:44:31

Daniel writes "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???"

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-05-15 : 08:58:59

basically you can use isnull, coalesce, case when to do this

update yourtable
set col_is_null = coalesce(col1, 0),
    col_is_date = case when col_is_date = '19000101' then null else col_is_date end

chiragkhabaria
Master Smack Fu Yak Hacker

1907 Posts

Posted - 2006-05-15 : 08:59:33

you dont have to write the 5 queries for this

Check out this

Update yourtable Set Col1 = 0, DateCol = null Where DateCol = '1/1/1900' or Col1 is null

If Debugging is the process of removing Bugs then i Guess programming should be process of Adding them.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources