I have a table with no Primary Key columns. First column is of type DATETIME named as DateTime, the rest are of type NVARCHAR(16). Values in the column AlarmStatus can be either '0' or '1'.
As such, my table may have multiple rows with the same DateTime and different AlarmStatus values. I neeed a way to delete ALL the rows with the same DateTime ONLY IF no one of them has a '1' in AlarmStatus column.
Add a comment
|
2 Answers
DELETE
FROM yourTable
WHERE DateTime IN
(
SELECT DateTime
FROM yourTable
GROUP BY DateTime
HAVING SUM(CASE WHEN AlarmStatus = '1' THEN 1 ELSE 0 END) = 0
)
Comments
This can be done with a correlated subquery: You want to delete rows for which no row with the same DateTime value and a set AlarmStatus exists:
DELETE FROM MyTable
WHERE NOT EXISTS (SELECT *
FROM MyTable AS T2
WHERE T2.DateTime = MyTable.DateTime
AND T2.AlarmStatus = '1');
Alternatively, get a list of all timestamps with the status you want to keep, and check that the row to be deleted does not have one of those:
DELETE FROM MyTable
WHERE DateTime NOT IN (SELECT DateTime
FROM MyTable
WHERE AlarmStatus = '1');
1 Comment
S. Neuer
Thanks @CL.! Working solutions too!