Tuesday, July 6, 2010

Delete duplicate records

Use this technique with small table only such as this example.^_^

DECLARE @Test TABLE (id int identity(1,1), a int, b int, c int)
INSERT INTO @Test(a,b,c)
SELECT 1,2,3 
UNION ALL SELECT 1,2,3 
UNION ALL SELECT 1,2,3
UNION ALL SELECT 2,3,4
UNION ALL SELECT 2,3,4
UNION ALL SELECT 3,4,5
UNION ALL SELECT 4,5,6
UNION ALL SELECT 4,5,6
UNION ALL SELECT 4,5,6
DELETE t2
FROM @Test t1
   INNER JOIN @Test t2 ON t1.a = t2.a AND t1.b = t2.b AND t1.c = t2.c
      AND t2.id > t1.id;
SELECT * FROM @Test

No comments:

Post a Comment

Oracle Query