How to delete duplicate records in SQL when similar in two columns and different in one column

Question

I have a table like this:

ID  Name    Family  Phone_Number
1   A       B       123456
2   c       d       321456
3   A       B
4   A       B       456789

I want to delete records 3 and 4.

Answer 1

Try to figure out duplicates and then delete the duplicate rows:

WITH cte AS (
  SELECT 
    FirstName
  , LastName
  , row_number() OVER(PARTITION BY FirstName, LastName ORDER BY FirstName) AS RN
  FROM YourTABLE
)
DELETE cte WHERE RN > 1

An example:

DECLARE @table TABLE 
(
    ID INT,
    FirstName VARCHAR(10),
    LastName  VARCHAR(10)
);

INSERT INTO @table
(
    ID,
    FirstName,
    LastName
)
VALUES
  (1, 'A' , 'B') 

, (2, 'c' , 'd') 

, (3, 'A' , 'B')

, (4, 'A' , 'B')

Query to delete:

;WITH cte AS (
  SELECT 
    FirstName
  , LastName
  , row_number() OVER(PARTITION BY FirstName, LastName ORDER BY FirstName) AS RN
  FROM @table
)
DELETE cte WHERE RN > 1

SELECT * FROM @table

OUTPUT:

ID  FirstName   LastName
1       A          B
2       c          d

Answer 2

Write sql and execute

; WITH TableBWithRowID AS
(
 SELECT ROW_NUMBER() OVER (ORDER BY Name, Family) AS RowID, Name, Family
 FROM TABLE1
)

DELETE o
FROM TableBWithRowID o
WHERE RowID < (SELECT MAX(rowID) FROM TableBWithRowID i WHERE i.Name =o.Name and i.Family=o.Family GROUP BY Name, Family)

replace TABLE1 with your table name

Answer 3

The below query will delete all the duplicates records based on the first and last name column. Assuming there is no null in the first and last name column. You just need to provide/change at two places in below query

DELETE FROM <YourTableName>
where Id not in (
   SELECT MIN(ID) as RowId 
   FROM <YourTableName>
   GROUP BY FirstName, LastName
) 

Answer 4

With EXISTS:

delete t from tablename t
where exists (
  select 1 from tablename 
  where name = t.name and family = t.family and id < t.id 
)

See the demo