need to deselect null values in a table

Question

need to deselect null values in a table

curious_sqldba

SSC-Dedicated

Points: 36494
More actions
November 4, 2008 at 6:32 pm

#128499

hello i need to change the constraint from allow nulls yes to no and i get the following error..plz help me
'DatabaseLog' table
- Unable to modify table.
Cannot insert the value NULL into column 'Object', table 'Adventureworks.dbo.Tmp_DatabaseLog'; column does not allow nulls. INSERT fails.
The statement has been terminated.
thx

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

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

RBarryYoung SSC Guru Points: 143329 More actions · Answer 1

You cannot disallow NULLS on that column, because there are still rows in the table that have NULL in that column.

[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung [font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]

curious_sqldba SSC-Dedicated Points: 36494 More actions · Answer 2

thank you for your reply...i did by right clicking on the table and den design ...i was trying to uncheck the allow nulls and it gave me an error..but i got a request to change it to do not "allow nulls"...is der anyway to do it...if no wat wld be the best way to convince her...thx..again..

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 3

Before you can set a column that currently allows NULL values to NOT ALLOW NULL values, you have to identify all the rows in the table where that column currently has a null value and update the column with a known value.

Once that is accomplished, then you set the column to not allow null values.

curious_sqldba SSC-Dedicated Points: 36494 More actions · Answer 4

THANK YOU for your reply..could you plz explain me in detail dat how wld i do dat...

RBarryYoung SSC Guru Points: 143329 More actions · Answer 5

Like this:

UPDATE Tmp_DatabaseLog

Set Object = {YourNullReplacementValue}

From Tmp_DatabaseLog

Where Object IS NULL

[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung [font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 6

Here is the skeleton code you would use:

update dbo.mytable set

column2update = somevalue

where

column2update is null;

What value you would use; that you, your developers, and your USERS will have to determine.

RBarryYoung SSC Guru Points: 143329 More actions · Answer 7

Race condition.

[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung [font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]

curious_sqldba SSC-Dedicated Points: 36494 More actions · Answer 8

thank you..got this error

Msg 208, Level 16, State 1, Line 1

Invalid object name 'Tmp_DatabaseLog'

i rlly dnt know where is it gtng 'Tmp_DatabaseLog' from..ders no such table der..thx

RBarryYoung SSC Guru Points: 143329 More actions · Answer 9

den put de right name in.

[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung [font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]