CODEWITHSUNDEEP
sqlt-sql
Kashif
Kashif

Reputation: 14440

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

Please see data given below alt text http://img709.imageshack.us/img709/1980/deleteduprecords.png

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.

Upvotes: 1

Views: 325

Answers (3)

priyanka.sarkar
priyanka.sarkar

Reputation: 26518

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

Upvotes: 0

Adriaan Stander
Adriaan Stander

Reputation: 166486

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

Upvotes: 1

Steve De Caux
Steve De Caux

Reputation: 1779

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)

Upvotes: 2

Related Questions