Delete set of duplicate records and Keep another set of duplicate record.

Question

Please see data given below

I want to keep one set of records and want to delete another duplicate set of records. You can see ForeignKey are same just Primary Key is different.

Need to keep 2 records having lowest primary key among the set of 4 records.

Answer 1

Delete from Table mytable t1
where exists (select 1 from mytable t2
where t2.PrimaryKey < t1.PrimaryKey
and t2.ForeignKey = t1.ForeignKey
and t2.AnotherForeignKey = t1.AnotherForeignKey)

Answer 2

Using CTE functionality from SQL Server 2005, you can delete Duplicate records as follows

(PS. I cannot see the design, so this is an approach.)

DECLARE @Table TABLE(
     ID INT,
     FK INT,
     VName VARCHAR(50)
)

INSERT INTO @Table (ID,FK,VName) SELECT 1, 1, 'A'
INSERT INTO @Table (ID,FK,VName) SELECT 2, 1, 'A'
INSERT INTO @Table (ID,FK,VName) SELECT 3, 1, 'A'
INSERT INTO @Table (ID,FK,VName) SELECT 4, 2, 'B'
INSERT INTO @Table (ID,FK,VName) SELECT 5, 2, 'B'
INSERT INTO @Table (ID,FK,VName) SELECT 6, 2, 'B'

;WITH CTE AS (
    SELECT *,
      ROW_NUMBER() OVER (PARTITION BY FK  ORDER BY ID) RowNumber
    FROM @Table
)
DELETE FROM CTE WHERE RowNumber > 1

SELECT * FROM @Table

Answer 3

Actually I am in a dilema that whether by SET you mean to say a combination of all the last 2 fields or only the foreign key.

If it is only foreign Key, accept Astender's solution.

If it is for last two fields, then

Sample input:

PK  FK AFK
4669    121 1
4670    121 2
10675   121 1
10676   121 2
101 254 1
102 254 2
703 254 1
704 254 2

Query:

;with cte as(
select
ROW_NUMBER() over(partition by FK ,AFK order by GETDATE()) rn,
t.* from @t t)
delete from cte where rn = 1
select * from @T

Output:

PK  FK AFK
4669    121 1
10676   121 2
102 254 2
703 254 1