CODEWITHSUNDEEP
t-sql
Wilest
Wilest

Reputation: 1860

T SQL Delete records based on a calculated field

I want to delete rows in my table where the answer for 6 fields is zero / null:

Delete
from TableA
WHERE (a + b + c + d + e + f) IS NULL

Thanks

Upvotes: 1

Views: 387

Answers (4)

gbn
gbn

Reputation: 432421

 WHERE COALESCE((a + b + c + d + e + f), 0) = 0

or

 WHERE NULLIF((a + b + c + d + e + f), 0) IS NULL

No need to have this for every column

Upvotes: 2

connectedsoftware
connectedsoftware

Reputation: 7087

Try this 

DELETE T FROM
  dbo.TableA T
  WHERE COALESCE(a, 0) + 
        COALESCE(b, 0) + 
        COALESCE(c, 0) +
        COALESCE(d, 0) +
        COALESCE(e, 0) +
        COALESCE(f, 0) = 0

See this SQLFiddle

Upvotes: 1

bendataclear
bendataclear

Reputation: 3850

Do you mean if al fields are null or if 1 or more field is null?

It may be as simple as:

delete
from TableA
WHERE (a + b + c + d + e + f) IS NULL

Upvotes: 0

Hitesh
Hitesh

Reputation: 3880

If you just want to delete those rows then you can use query like 

DELETE FROM TableName
WHERE (a + b + c + d + e + f) IS NULL OR (a + b + c + d + e + f) = 0

I hope it will help you. :)

Upvotes: 0

Related Questions