mdarr mdarr - 7 months ago 10
SQL Question

Delete group of rows with specific ID conditional on another column

I have a table with a couple thousand entries that looks like this:

ID Data Type Amount
1 Start
1 Money 13.45
1 Money 3.79
1 Money 46.82
1 END
2 Start
2 Money 26.24
2 END
3 Start
3 END


I would like to remove any groups of ID's that look the way that ID=3 looks in the example above where there is only a Start/END but no money. I'm trying to use an inner join to identify these rows but I'm having trouble forming my select statement. How do I select the rows that fit this description?

APH APH
Answer

Simple option:

Delete from MyTable
Where ID not in (Select ID from MyTable where [Data Type] = 'Money')