SQL Server - Remove Null rows from a Table

Asked By Jr .net on 05-Feb-08 08:54 PM

I want to delete a complete row where one of its column value is NULL, I tried like

Delete from MyTable where FirstName is NULL or ' '

Advance thnx

give this query a try for your deletion of rows with column values=null

sundar k replied to Jr .net on 05-Feb-08 11:11 PM

Try this query

delete from MyTable where FirstName is NULL or len(rtrim(ltrim(FirstName)))=0

NULLIF

F Cali replied to Jr .net on 05-Feb-08 11:04 PM

You can also use the NULLIF function for this:

DELETE FROM [dbo].[MyTable] WHERE NULLIF(RTRIM([FirstName]), '') IS NULL

How do you get Null values at the first place

Nenad Prekupec replied to Jr .net on 06-Feb-08 03:19 AM

As it seems to me you are doing things on the wrong way.
Data base itself has menas to protect from such kind of things automaticly, defining constraints at database level will give you less work for keeping your DB clean of such things, the only thing you have to do is handle errors from DB (this is what you have to do anyway) and you have to say what fields in tables you don't want to have NULL values, that way it's impossible to have your situation in the first place.