Help with this scenario

[techytechy]

Hi guys,

Need help with writing the query with this scenario:

myTable:
---------------------------
ID Name status
---------------------------
1 Name1 good
2 Name2 bad
...
3 Name1 Bad
4 Name1 worst
-----------------------------
If a Name with status = good has been found and if there is another record on the same Name then those records need be deleted including itself.

In the above the records with Name1 needs to be deleted as it appeared again after having status = good

Thanks!

 

[RiverGuy]

DELETE a
FROM myTable a
INNER JOIN
     (SELECT * FROM myTable b
      WHERE [status] = 'good') c
ON a.[Name] = c.[Name]