Friday, June 8, 2012

SQL - Remove duplicated records


-- Remove duplicated records
DECLARE @DupDemo TABLE
(
   A VARCHAR(15),
   B VARCHAR(15),
   C VARCHAR(15)
)

-- Insert testing records
INSERT INTO @DupDemo VALUES
('1','a','2'),
('1','a','1'),
('2','c','6'),
('2','c','3'),
('1','a','1'),
('3','b','3')

-- Original Records
SELECT *
FROM @DupDemo

-- Show the duplicated records, only to check column A and B.
SELECT ROW_NUMBER() OVER(PARTITION BY A,
                                      B
                    ORDER BY C) AS ID,
             A,
             B,
             C
      FROM @DupDemo

-- Delete the duplicated records
DELETE D
FROM(
      SELECT ROW_NUMBER() OVER(PARTITION BY A,
                                            B
                               ORDER BY C) AS ID,
             A,
             B,
             C
      FROM @DupDemo
)D
WHERE D.ID > 1

-- After delete operation
SELECT *
FROM @DupDemo

-- Show DEMO 2
DELETE FROM
@DupDemo

-- Insert test records
INSERT INTO @DupDemo VALUES
('1','a','2'),
('1','a','1'),
('2','c','6'),
('2','c','3'),
('1','a','1'),
('3','b','3');

-- With TEMP table
WITH Dup AS
(
   SELECT ROW_NUMBER() OVER(PARTITION BY A,
                                            B
                               ORDER BY C) AS ID,
             A,
             B,
             C
      FROM @DupDemo
)

DELETE FROM
Dup
WHERE ID > 1;

-- Show records after delete operation.
SELECT *
FROM @DupDemo

No comments:

Post a Comment