Search code examples
sqlsql-serversql-delete

Remove a registry if any column has a NULL value in SQL Server 2008

I would like to delete the rows of a table (Creating other one as the updated version )if any of them has a null value

for example

att1  att2  att3
-----------------
1.0    2.9   NULL
23.6   7.9   8.9
43.6   6.9   1.9

AND GET

att1  att2  att3
-----------------
23.6   7.9   8.9
43.6   6.9   1.9

How would be the best approach to do this?

Solution

  • Simplest way 

    DELETE FROM T 
WHERE  att1 IS NULL 
        OR att2 IS NULL 
        OR att3 IS NULL

    If you have loads of columns at some point this might become simpler.

    DELETE FROM T 
WHERE  EXISTS (SELECT * 
               FROM   (VALUES(att1), 
                             (att2), 
                             (att3)) V(att) 
               WHERE  att IS NULL)

    You might also consider something like

    DELETE FROM T 
WHERE att1 + att2 + att3 IS NULL

    but that could cause unneeded problems with overflow errors potentially.