Delete Duplicate record in sql server if 2 colums matching

Question

Col1	Col2	Col3
A	B	1
A	B	1
A	B	2
A	B	2
A	c	1

When col1 and Col2 values are same and Col3 values are different I dont want that values in result set. I want result as below. I tried with row_number, group by , so manythings but did not worked. Please help me here

Col1	Col2	Col3
A	c	1

Does this help? https://stackoverflow.com/questions/6025367/t-sql-deleting-all-duplicate-rows-but-keeping-one — WaitingForGuacamole, Feb 26 '21 at 16:33

score 1 · Answer 1 · answered Feb 26 '21 at 12:46

You can use exists:

delete from t
    where exists (select 1
                  from t t2
                  where t2.col1 = t.col1 and t2.col2 = t.col1 and
                        t2.col3 <> t.col3
                 );

You can also use window functions:

with todelete as (
      select t.*,
             min(col3) over (partition by col1, col2) as min_col3,
             max(col3) over (partition by col1, col2) as min_col4
      from t
     )
delete from todelete
     where min_col3 <> max_col3;

score 0 · Answer 2 · answered Feb 26 '21 at 13:56

Best way is to make these column a unique composite key. But here is a query to delete all records other than your desired result.

delete from Table_1 
where 
Col1=(SELECT Col1
      FROM table_1
      GROUP BY Col1, Col2
      HAVING Count(*) > 1) 
And 
Col2 =(SELECT Col2
       FROM table_1
       GROUP BY Col1, Col2
       HAVING Count(*) > 1)

this might not be the most optimized and efficient query but it works. if you don't want to delete duplicated records and just retrieve unique ones:

SELECT Col1,Col2
FROM table_1
GROUP BY Col1, Col2
HAVING Count(*) = 1

To get duplicating records:

SELECT Col2,Col1
FROM table_1
GROUP BY Col1, Col2
HAVING Count(*) > 1

Delete Duplicate record in sql server if 2 colums matching

2 Answers2