SQL delete unique row in table

Question

I've got the following table:

PatientID | DiagID

...where PatientID is a unique identifier and DiagID is a diagnosis ID, which is not unique. How do I delete the patient from table who's got unique DiagID (the only in the table, who's got this specific DiagID)?

Answer 1

Presumably you want to delete any patients that only have a single instance of a diagnosis ID. In which case the SQL would be something like:

DELETE Patient
WHERE DiagID in
(SELECT DiagID FROM Patient GROUP BY DiagID HAVING COUNT(*) = 1)