CODEWITHSUNDEEP
sql-server
Gargoyle
Gargoyle

Reputation: 10365

Simplify SQL deletion query

I have an AuditAnswersTable which contains the answers to audit questions, and it has the id of the entry from the AuditTable that the question related to.

I now want to delete all audits where there are no answers provided. I have this working query, but I'm feeling like there has to be an easier way?

DELETE FROM AuditTable
WHERE id IN (
    SELECT id FROM AuditTable
    WHERE id NOT IN (
        SELECT DISTINCT audit_id
        FROM AuditAnswersTable
    )
)

Upvotes: 0

Views: 20

Answers (2)

Russell Fox
Russell Fox

Reputation: 5445

You can avoid the sub-select with a LEFT JOIN:

DELETE a
FROM AuditTable a
    LEFT JOIN AuditAnswersTable t
        ON a.id = t.audit_id
WHERE t.audit_id IS NULL ;

Upvotes: 2

Steve Land
Steve Land

Reputation: 4862

Yes.

DELETE FROM AuditTable
WHERE id NOT IN (
    SELECT DISTINCT audit_id
    FROM AuditAnswersTable
)

Upvotes: 0

Related Questions