CODEWITHSUNDEEP
sqlsql-servert-sql
user3115933
user3115933

Reputation: 4443

How to delete rows in a table based on this specific condition without mentioning the column names?

I need to clean several tables on my SQL Server database by deleting all rows where all the columns in the table have NULL values or are Blanks.

I know I can do it using CASE Statements or filtering the columns in my query.

Example:

Delete from [T1]

WHERE [col1] IS NULL OR [col1] = ''
AND [col2] IS NULL OR [col2] = ''
AND [col3] IS NULL OR [col3] = ''

It would be quite tedious to write the column names for each relevant table. I am looking for a single T-SQL query that would allow me to do so simply by replacing the table name in the query.

Is this possible?

Upvotes: 0

Views: 1907

Answers (1)

Tim Biegeleisen
Tim Biegeleisen

Reputation: 521249

Perhaps not a complete answer or solution, but your WHERE clause could be somewhat simplified:

DELETE
FROM T1
WHERE COALESCE(col1, '') + COALESCE(col2, '') + COALESCE(col3, '') = '';

Upvotes: 1

Related Questions