SQLTeam.com | Weblogs | Forums

I would love some help with my DELETE-Query



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 round0001. 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?


one way WITH cte AS (SELECT DISTINCT Round FROM table WHERE Corrected = 1) DELETE T FROM table T INNER JOIN cte on T.Round = cte.Round WHERE T.Corrected = 0;