sqlduplicatesexists

Delete rows based on a condition of another column

here is an image of it

I am trying to work in SQL server just its easier to show it in Excel.

I have two columns: cardnumber and comment. If one cardnumber exists twice in my database and it has a row with a comment "corrected" and also exists with the comment "closed" then I need to delete the line with the comment "corrected". I have no idea how to manage it, I tried with exists but it does not work. Thank you all.

I tried

SELECT 
* 
FROM TABLE1 
WHERE EXISTS(SELECT CARDNUMBER, COMMENT FROM TABLE1 WHERE COMMENT IN ('CORRECTED', 'CLOSED')

But it does not work.

Solution

  • Here is one approach using a deletable CTE:

    WITH cte AS (
    SELECT *, COUNT(CASE WHEN comment = 'closed' THEN 1 END)
                  OVER (PARTITION BY cardnumber) AS closed_cnt
    FROM Table1
)

DELETE
FROM cte
WHERE comment = 'corrected' AND closed_cnt > 0;