T-SQL DELETE statement. Only delete rows of certain criteria based on other row

Question

This is a simplified table of Cancer-examinations.:

Examination     Round           Corrected
-------------------------------------------
exam001         round001            0
exam002         round001            1
exam003         round002            0
exam004         round002            1
exam005         round003            0
exam006         round003            0

I want to write a query that removes exam001 and exam003.

Lets look at round001. It has exam001 and exam002 connected to it. But since exam002 is corrected, we know that there was something wrong with exam001. Hence, I want to remove it. I do not want to delete any exams for round003, since none of them are corrected.

So in pseudocode:

foreach round
delete all exams where corrected == 0
if round has exam where corrected == 1

It is given that every round has exactly 2 exams.

How would I do this?

Answer 1

You should use EXISTS() for that matter .

Delete all corrected = 0 if a row exists for the same round with corrected 1

DELETE FROM Cancer-Examinations t
WHERE t.Corrected = 0
AND EXISTS(SELECT 1 from Cancer-Examinations s where t.round = s.round and s.corrected = 1)