Query a NULL Record

[david6633]

Is there an easy way to test, in a query, whether all of the fields in a record are NULL?

I appreciate that it is unusual to create a table that contains all null records but the circumstance that I have requires it.

David
Remember: You only know what you know
and - you don't know what you don't know!

 

[r937]

the easy way is to recognize that the primary key of the table can never be null, and therefore you can never have a row with null in all the columns

okay, once you realize you should've created a primary key and didn't, the next step is to ask yourself what you're going to do if you ever did find a row where every column is null -- there would be no reason to SELECT it, right? i mean, why go to the trouble of pulling it out, since you already know what all the columns will be?

the only thing you can possibly want to do with it is delete it -- you can't want to update it, because that would be the same as deleting it and then inserting a row with actual values

so...

DELETE
FROM yourtable
WHERE column1 IS NULL
AND column2 IS NULL
AND column3 IS NULL
AND ...

and then, as a final step, declare a primary key, so this will never happen again



r937.com | rudy.ca