Nulls

Can someone tell me what to do if several NULLS are showing up within tables. Is there an easy step to locate all NULLS and delete them within the table?

Can someone provide detailed infromation on how to delete NULLS within tables. I'm new o the SQL side so I hope I have explained what it is that I'm looking to do. Our in house programmer said that the NULLS maybe causing a problem with the software communicating with the SQL Server software.to locate all records having nulls in a talbe:
select * from table where column_name is null

to delete all rows that contain nulls:
delete from table where column_name is null

Richard

Originally posted by Arra2
Can someone tell me what to do if several NULLS are showing up within tables. Is there an easy step to locate all NULLS and delete them within the table?

Can someone provide detailed infromation on how to delete NULLS within tables. I'm new o the SQL side so I hope I have explained what it is that I'm looking to do. Our in house programmer said that the NULLS maybe causing a problem with the software communicating with the SQL Server software.|||Originally posted by Arra2
Can someone tell me what to do if several NULLS are showing up within tables. Is there an easy step to locate all NULLS and delete them within the table?

Can someone provide detailed infromation on how to delete NULLS within tables. I'm new o the SQL side so I hope I have explained what it is that I'm looking to do. Our in house programmer said that the NULLS maybe causing a problem with the software communicating with the SQL Server software.

Do you want to delete the whole record that contains the nulls?

you can find all teh nulls by usuing:

select * from tableName
where whateverField = '0'

Just fill in the tableName part with the table that you are looking in and
fill in the whateverField part with the name of the column that the nulls appear in.|||Originally posted by estefex
Do you want to delete the whole record that contains the nulls?

you can find all teh nulls by usuing:

select * from tableName
where whateverField = '0'

Just fill in the tableName part with the table that you are looking in and
fill in the whateverField part with the name of the column that the nulls appear in.

Correction on how to find nulls:

select * from tableName
where whateverField is null|||Sory i didn't explain in more detail. i can find the NULLS no problem.
Once I find the NULLS I would like to delete the NULLS ONLY and not the whole column where the NULLS are located. I want to replace the viewable NULL with nothing.

I would like to delete the NULL but keep everything else intact.|||The only way to make the NULL values go away without deleting the row is to replace the NULL with another value, something likeUPDATE myTable
SET dateCol = GetDate()
WHERE dateCol IS NULLThe only way to make the NULL value "go away" is to put something else in its place.

-PatP|||Originally posted by Arra2
I want to replace the viewable NULL with nothing.

i think this is the real problem

Arra2 is using some software to display table contents, and that software is substituting something for the nulls

e.g. the word NULL

how about using COALESCE in your queries?
select coalesce(nullablecharfield,'') as displayablecharfield

of course, if you have a nullable numeric field, you have another problem on your hands, don't you|||I've tested your information and it's what I'm looking for. Thanks.

But what if I what the NULL to be replaced with nothing. I want a blank field.

If I try

Update My_table_name
Set My_Column_name =
Where My_Column_Name is Null

I recieve an error. I want to replace the Null with a blank field.

Thanks for the help up to this point.|||Yup, numeric, datetime, and bit will be a problem. But I think he's talking about character NULLs. How about char(39)+char(39)?|||Originally posted by Arra2
But what if I what the NULL to be replaced with nothing.
um, i think you might want to re-think this

NULL is nothing

a "blank" field contains a string

it's a zero-length string to be sure, but it's not nothing
I want to replace the Null with a blank field.
ah, well, if you insist --update my_table_name
set my_column_name = ''
where my_column_name is null
you're still gonna have trouble with numeric fields, though|||If you are going to go through all this trouble:

1. Redesign so you don't have NULL attributes in your entities.
or
2. Just learn how to use NULL. Especially if you are wanting to use this for all kinds of datatypes like integer, money, etc.|||Correction on how to find nulls:
select * from tableName
where whateverField is null

It is extremely important to notice the fundamental difference between the original test (whateverField = '0') and this, correct one.

NULL is defined as "the absence of any value at all." The value in the field is not zero, nor any other value: it is, "no value at all." Therefore, it is not "equal to" (nor, for that matter, "unequal to") any value at all.

Consider a table with a person's name and his age. Tom is 50 and Dick is 30, but Mary declined to tell us her age. Therefore, the age-column in her record is for the moment NULL. If we then "SELECT ... AVERGAE(AGE)" we'll get the correct value: 40. SQL took the two values which did exist (50 and 30), added them up, divided by the count of values which did exist (2, not 3), and produced the expected result. This is fundamentally important in statistics, where NULL conveniently represents a "missing value."|||Thanks for all those who have helped. I think I have it under control.

Thanks,

SQL Rookie