How do you delete a row if one column in the row is NULL

1

Hi all

I have a SQL code that after it runs some rows have columns that have NULL in the field. How do I delete all rows that have NULL in any column? Any assistance would be appreciated.

2

DELETE FROM dbo.table_name
WHERE col1 IS NULL OR col2 IS NULL OR ...

3

Thanks Scott...That is good if you have 4 or 5 columns or so, but what if you have upwards to 30+ columns? but at the risk of sounding lazy, Is there a way to do that w/o having to list each column? If not that's ok, I was just hoping that there was.

4

No, but you can easily generate the code, you don't have to write it by hand:

SELECT 'OR [' + name + '] IS NULL '
FROM sys.columns
WHERE object_id = OBJECT_ID('dbo.your_table_name')
ORDER BY column_id
1 Like
5

What is the code that is generating the rows with a NULL value? Wouldn't it be better to eliminate the rows that you don't want in this query instead of trying to remove the bad data after generating the results?

1 Like