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

n  Posted By : MR. JOYDEEP DAS