Set to NULL

Question

Post reply

Set to NULL

nikosdra

SSC Rookie

Points: 39
More actions
September 6, 2003 at 3:00 pm

#81964

Hello all,
How can I update an "int" field and set it to NULL? What is the correct sql syntax?
Thanks

Viewing 10 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply

ray_higdon SSCarpal Tunnel Points: 4589 More actions · Answer 1

update table set column = null

where ...

Are you having problems with setting the column to null? Does it allow nulls or is it an identity column?

HTH

Ray Higdon MCSE, MCDBA, CCNA

------------

Ray Higdon MCSE, MCDBA, CCNA

Gabor Nyul SSCrazy Eights Points: 9459 More actions · Answer 2

But to do what Ray suggested you must be sure you column is nullable. If not you have to make it nullable by using the ALTER TABLE command. See BOL

Bye

Gabor

Bye
Gabor

Frank Kalis SSC Guru Points: 111183 More actions · Answer 3

quote:
How can I update an "int" field and set it to NULL? What is the correct sql syntax?

do you really want to set it to NULL or 0?

Setting to NULL will add some 'wasted storage overhead' to your db, I think, because SQL Server maintains a bit map column in every row to keep track of the columns there are actually really NULL. This has to be decoded for every accessed row.

In addition to this, is this field used in an index, for a PK-FK relation or for JOIN operations?

Frank

--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]

satishbt SSC Eights! Points: 828 More actions · Answer 4

It is recommended not to use NULL in any of tables and instead update it with some default values. This case, since it is an INT

field, it is better that you default it with 0.

And I do agree with Frank, if this field is part of any of Composite PK or PK itself, or FK, then u will not be able to update it to NULL. For that matter, updating any Field to NULL is not recommended, for the reasons what Mr. Frank has quoted. Hope this helps.

Frank Kalis SSC Guru Points: 111183 More actions · Answer 5

Hi satishbt,

quote:
And I do agree with Frank, if this field is part of any of Composite PK or PK itself, or FK, then u will not be able to update it to NULL. For that matter, updating any Field to NULL is not recommended, for the reasons what Mr. Frank has quoted. Hope this helps.

just out of curiosity.

Satish Teli or Teli Satish ?

I knew this only from korean people to say their last name and then first name.

Is it in India the same?

Frank

--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]

cvandemaele SSC Veteran Points: 208 More actions · Answer 6

Hello to you,

It's not the first time that I read something about allowing columns to take NULL values not begin recommended.

To my point of view, there is a valid opposite argument to every reason mentioned so far in this thread. For example, it is simply not true that you cannot use "null-allowed" columns in foreign keys. To my point of view, it is just the other way around.

Now I have the following question : did anybody at Microsoft, Oracle, etc. ever released some kind of official statement about null-values not being recommended when working with their respective databases ?

Thanks for putting some light in my troubled brain ?

CVM.

Frank Kalis SSC Guru Points: 111183 More actions · Answer 7

quote:
It's not the first time that I read something about allowing columns to take NULL values not begin recommended.
To my point of view, there is a valid opposite argument to every reason mentioned so far in this thread. For example, it is simply not true that you cannot use "null-allowed" columns in foreign keys. To my point of view, it is just the other way around.
Now I have the following question : did anybody at Microsoft, Oracle, etc. ever released some kind of official statement about null-values not being recommended when working with their respective databases ?

first of all, NULL has a legitimation of existence. Codd stated this in his twelve rules, because NULL is simply 'unknown'!

So any RDBMS in order to comply with SQL92 I think must offer systematic threatment of NULL values.

Of course, there are certain cases where using NULLs is appropriate.

If, for example, an FK relationship is optional, the FK column must allow for NULLs

Frank

--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]

satishbt SSC Eights! Points: 828 More actions · Answer 8

Hi Frank, I'm from India-B'lore and Name is Satish(1st name), Teli(last name) is my SIR name.

cvandemaele, as far as u'r argument goes, at times just allowing the field to be NULL by default is fine. But, come to think of it in real time situation where the field is refered extensively across the application, SP, Trig,etc etc. You will have have to check for the NULL value during comparision. Of course, there is no written rule as such.

But, with experience atleast, I have observered that using Default values bails you out in few unforseen cases. I had quite a few problems with NULLs in the tables.

ray_higdon SSCarpal Tunnel Points: 4589 More actions · Answer 9

Frank is right, a classic example was a man in California had a license plate that said "none". When he received a traffic ticket he was held liable for thousands of unpaid tickets for cars that had no license plate,as the police had no way to track a car not having a plate versus a car with a plate of "none". 0 does not mean null, in relational theory there is true, false or unknown (null)

Ray Higdon MCSE, MCDBA, CCNA

------------

Ray Higdon MCSE, MCDBA, CCNA