How to update table column in SQL Server only wherever null appears?

Question

I have a SQL Server table with more than 15 columns.

One of my column name is verification_Status. Currently, I have 0, 1, 2, null values under verification_Status as below:

For example:

Id     Name      verification_Status
1       John       0
2       Kat        1
3       Williams   Null
4       Rosy       null

I want to make 0 wherever null appears. I have 4k rows to update so I am little worried.

update masterTable 
set verification_Status = 0 
where verification_Status == null

I am planning to use above query.

May I know this is the right query to my problem? Can someone guide me on this?

you are almost there..just use where verification_Status is null — Vamsi Prabhala
– Vamsi Prabhala, Commented Nov 29, 2016 at 4:52
4K records, even with 15 columns shouldn't see a performance hit if it is a one time or scheduled job. Now if you were doing this every second then I would consider setting the default column attribute to 0 so it is always a 0 on insert if NULL. — ioneyed
– ioneyed, Commented Nov 29, 2016 at 4:54

CL. · Accepted Answer · 2017-09-18 07:59:30Z

3

Use this:

update masterTable 
set verification_Status = 0 
where verification_Status is null

<> is Standard SQL; != is its T-SQL equivalent. Both evaluate for values, which NULL is not -- NULL is a placeholder to say there is the absence of a value.

Which is why you can only use IS NULL / IS NOT NULL as predicates for such situations.

This is standard of all kinds of SQL management platforms

refer this: Null (SQL)

edited Sep 18, 2017 at 7:59

CL.

182k18 gold badges241 silver badges282 bronze badges

answered Nov 29, 2016 at 4:52

Vikrant

5,05218 gold badges53 silver badges77 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Vikrant Over a year ago

@NiranjanGodbole, if it meets your requirement you can consider it to accept as your Answer

Niranjan Godbole Over a year ago

yes but it says wait 6 minutes to make answer as marked

Tharunkumar Reddy · Accepted Answer · 2016-11-29 04:57:27Z

3

Your query should be

update masterTable set verification_Status=0 where verification_Status is null

Because NUll=NUll alwaysFalse. So the records which will have value as NULL in verification_status will not update zero. so you should use Verification_status is null at where clause.

edited Nov 29, 2016 at 4:57

answered Nov 29, 2016 at 4:52

Tharunkumar Reddy

2,81320 silver badges33 bronze badges

Comments

ioneyed · Accepted Answer · 2016-11-29 04:56:14Z

1

Periodic update of value:

update masterTable set verification_Status=0 where verification_Status is null

Permanent Default Value on any table insert:

ALTER TABLE {TABLENAME} 
ADD {COLUMNNAME} {TYPE} {NULL|NOT NULL} 
CONSTRAINT {CONSTRAINT_NAME} DEFAULT {DEFAULT_VALUE}
[WITH VALUES]

answered Nov 29, 2016 at 4:56

ioneyed

1,1027 silver badges12 bronze badges

Comments

Munavvar · Accepted Answer · 2016-11-29 05:12:30Z

1

Check number of rows will affect before updating.

Select Count(*) from masterTable WHERE ISNULL(verification_Status,'') = ''

It is possible you also have empty string instead of null value in some columns.

If you want to update empty values and null values then use this.

UPDATE masterTable SET verification_Status=0 WHERE ISNULL(verification_Status,'') = ''

edited Nov 29, 2016 at 5:12

answered Nov 29, 2016 at 5:01

Munavvar

8312 gold badges11 silver badges33 bronze badges

2 Comments

Vikrant Over a year ago

It includes other values also which are '' (blank) and NOT NULL! And OP says, I want to make 0 wherever null appears.

Munavvar Over a year ago

Made changes in answer

Collectives™ on Stack Overflow

How to update table column in SQL Server only wherever null appears?

4 Answers 4

2 Comments

Comments

Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

2 Comments

Comments

Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related