Please help a newbie out. Here is my original table (Table1).
I need to DELETE the original row that has the cancel. So in this example, row 1,3, and 4 are the same except for the status of 3. I need to find ALL the "Cancel" and DELETE the TOP 1 of the same Approve status. I need Top 1 because you can see row 4 was entered again after the cancel and becomes the permanent row that I need to keep. There are multiples of these in the Table1.
After it's done, Table1 should look like this.
(UPDATE: I've added more data to make sure it's clear I want to find ALL of the cancels. Please see below. My results should give me RowID 1 and 8)
Here's my logic but I don't know how to do it.
- Select Account, Type, Amount from Table1 where Status = 'Cancel'
- Select TOP (1) RowID from Table1 where {[this is the part I don't know. I would like the where to match all the criteria from the above Select]}
- If I can get 2) to work, then I would just get the original RowID entry which basically means the original entry and then I can just Delete From Table1 Where RowID in (above [3] Select statement)
- Then all I have to do is Delete all rows with the status of Cancel and that should get me my final table.
Please help!!! Thanks!