CODEWITHSUNDEEP
sqlite
S. Neuer
S. Neuer

Reputation: 111

SQLite. Complex condition in DELETE query?

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. enter image description here

Upvotes: 2

Views: 457

Answers (2)

CL.
CL.

Reputation: 180020

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');

Upvotes: 1

Tim Biegeleisen
Tim Biegeleisen

Reputation: 521053

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
)

Upvotes: 2

Related Questions