Deleting based on a Select with a Where that's using a Select..complicated

Transact-SQL
1

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.

  1. Select Account, Type, Amount from Table1 where Status = 'Cancel'
  2. 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]}
  3. 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)
  4. 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!

2

If you post directly useable data (CREATE TABLE and INSERT statements for the sample data), I'll post sample code to do that.

3

Thank you Scott.

CREATE TABLE Table1
(
RowID int,
Account varchar(255),
Type varchar(255),
Amount Decimal(18,2),
Status varchar(255)
);

Insert Into Table1 (RowID, Account, Type, Amount, Status) Values (1, 'John', 'Food', 12.75, 'Approve')
Insert Into Table1 (RowID, Account, Type, Amount, Status) Values (2, 'Mary', 'Drinks', 32.89, 'Approve')
Insert Into Table1 (RowID, Account, Type, Amount, Status) Values (3, 'John', 'Food', 12.75, 'Cancel')
Insert Into Table1 (RowID, Account, Type, Amount, Status) Values (4, 'John', 'Food', 12.75, 'Approve')
Insert Into Table1 (RowID, Account, Type, Amount, Status) Values (6, 'Sam', 'Toys', 2.89, 'Approve')
Insert Into Table1 (RowID, Account, Type, Amount, Status) Values (7, 'Phil', 'Gas', 1.75, 'Cancel')
Insert Into Table1 (RowID, Account, Type, Amount, Status) Values (8, 'Phil', 'Gas', 1.75, 'Approve')
Insert Into Table1 (RowID, Account, Type, Amount, Status) Values (9, 'Phil', 'Gas', 1.75, 'Approve')

Select * from Table1

4 
;WITH cte_find_rows_to_delete AS (
    SELECT t1_to_be_deleted.RowID
    FROM Table1 t1_cancel
    CROSS APPLY (
        SELECT TOP (1) t1_cancelled.*
        FROM Table1 t1_cancelled 
        WHERE
            t1_cancelled.Account = t1_cancel.Account AND 
            t1_cancelled.Type = t1_cancel.Type AND 
            t1_cancelled.Amount = t1_cancel.Amount AND
            t1_cancelled.RowID < t1_cancel.RowID
        ORDER BY t1_cancelled.RowID DESC
    ) AS t1_to_be_deleted
    WHERE t1_cancel.Status = 'Cancel'
)
SELECT * FROM cte_find_rows_to_delete
/* uncomment after testing :-)
DELETE FROM t1
FROM Table1 t1
INNER JOIN cte_find_rows_to_delete ON cte_find_rows_to_delete.RowID = t1.RowID
*/
5

Scott,

I really appreciate your help. That worked and I was able to get RowID 1 like I wanted. Like my original post says, I have multiples of these I have to Delete. So if I add more data, I'm still only getting RowID 1 as a result. If you add the following:

Insert Into Table1 (RowID, Account, Type, Amount, Status) Values (6, 'Sam', 'Toys', 2.89, 'Approve')
Insert Into Table1 (RowID, Account, Type, Amount, Status) Values (7, 'Phil', 'Gas', 1.75, 'Cancel')
Insert Into Table1 (RowID, Account, Type, Amount, Status) Values (8, 'Phil', 'Gas', 1.75, 'Approve')
Insert Into Table1 (RowID, Account, Type, Amount, Status) Values (9, 'Phil', 'Gas', 1.75, 'Approve')

The final result should give me RowID 1 and 8. Sometimes the Cancel comes before the Approve that needs to be deleted.

I'm sorry I didn't give you these second set of Inserts. I just thought it would work. I'm editing the OP to include these inserts.

Again - really appreciate the help

Phil

6

Sometimes the Cancel comes before the Approve that needs to be deleted.

Yikes. How would any logic know whether to match one before or after the cancel then?

7

I really don't know. It's coming from another system and there's no rhyme or reason on what order they send it to the DB.

Maybe what I need to do is write a stored procedure and use a loop. I've never done that before.

For each time it comes across CANCEL, store the Account, Type, Amount into a variable.
Then delete TOP (1) where it matches those values and the status is APPROVE.

And just keep looping till it sees the last CANCEL.

Any idea how I can do that?

8

I haven;t read the whole thread, but where things need to happen "in order" I allocate an incrementing ID number to them so that I can ORDER BY that ID. As far as the RDBMS is concerned there is no implicit order in a Set of data rows - the order in which they were inserted has no bearing (unless there is an incrementing ID or Data/Time etc. which can be used)

9

Try this

with ct1
as
(
select ROW_NUMBER()over(partition by t1.Account order by t1.Account) as ct, t1.* from #T1 as t1
)
,
ct2
as
(
select max(c.ct) over(partition by c.Account order by c.Account) as mx, c.* from ct1 as c
)
,
finalCte
as
(
select * from ct2 as c2
where c2.ct < c2.mx
)

--select the rows that will be deleted
select * from  finalCte

--Delete finalCte