Sunday 22 April 2012

How to Remove duplicate entry from a Table


It's a quite simple SQL manipulation for dulplicate entry delitions. Some how I am noted it down for a quick references.

-- Step-1 [ Create a Table ]

CREATE TABLE AA
            (x DECIMAL,
             y VARCHAR)

-- Step-2 [ Insert Some values, it must contains duplicate values ]

INSERT INTO AA
VALUES(1,'A'),(2,'B'),(3,'C'),(1,'A'),(1,'A')

-- Step-3
SELECT x, y, col3=count(*)
INTO midtable
FROM AA
GROUP BY x, y
HAVING count(*) > 1

--Step-4
SELECT DISTINCT AA.*
INTO midtable1
FROM AA, midtable
WHERE AA.x = midtable.x
AND AA.y = midtable.y

--Step-5
SELECT x, y, count(*)
FROM midtable1
GROUP BY x, y

--Step-6
DELETE AA
FROM AA, midtable
WHERE AA.x = midtable.x
AND AA.y = midtable.y

--Step-7
INSERT AA SELECT * FROM midtable1

--Step-8 [ Final Output ]
SELECT * FROM AA
 Posted by : MR. JOYDEEP DAS

No comments:

Post a Comment