How to delete Duplicate records from table

i have table and in that i have number of records which are duplicated.
i want to delete the duplicate records from this table.
pls help me.CREATE TABLE #Demo (
idNo int identity(1,1),
colA int,
colB int
)
INSERT INTO #Demo(colA,colB) VALUES (1,6)
INSERT INTO #Demo(colA,colB) VALUES (1,6)
INSERT INTO #Demo(colA,colB) VALUES (2,4)
INSERT INTO #Demo(colA,colB) VALUES (3,3)
INSERT INTO #Demo(colA,colB) VALUES (4,2)
INSERT INTO #Demo(colA,colB) VALUES (3,3)
INSERT INTO #Demo(colA,colB) VALUES (5,1)
INSERT INTO #Demo(colA,colB) VALUES (8,1)
PRINT 'Table'
SELECT * FROM #Demo
PRINT 'Duplicates in Table'
SELECT * FROM #Demo
WHERE idNo IN
(SELECT B.idNo
FROM #Demo A JOIN #Demo B
ON A.idNo <> B.idNo
AND A.colA = B.colA
AND A.colB = B.colB)
PRINT 'Duplicates to Delete'
SELECT * FROM #Demo
WHERE idNo IN
(SELECT B.idNo
FROM #Demo A JOIN #Demo B
ON A.idNo < B.idNo -- < this time, not <>
AND A.colA = B.colA
AND A.colB = B.colB)
DELETE FROM #Demo
WHERE idNo IN
(SELECT B.idNo
FROM #Demo A JOIN #Demo B
ON A.idNo < B.idNo -- < this time, not <>
AND A.colA = B.colA
AND A.colB = B.colB)
PRINT 'Cleaned-up Table'
SELECT * FROM #Demo
DROP TABLE #Demo
"shiva" <bany.shanker@.gmail.com> wrote in message
news:1133445445.278560.128440@.g14g2000cwa.googlegroups.com...
>i have table and in that i have number of records which are duplicated.
> i want to delete the duplicate records from this table.
> pls help me.
>|||Unfrotunately, I have not found a clean way to do this. Uri's method will
work great, but technically, his table doesn't have duplicate records (as th
e
identity column prevents that).
It you have true duplicates, here's what I do:
1) Create a table containing all the table's fields plus a count field.
CREATE TABLE #xxx
(
Field1 int,
Field2 int,
NumToDelete int
)
2) Select the records which are duplicate:
INSERT INTO #xxx
SELECT *, COUNT(*) - 1
FROM dupedtable
GROUP BY Field1, Field2
HAVING COUNT(*) > 1
3) Either loop or generate a cursor and go through each record in #xxx
perfoming the following (note, if you loop, you'll need an identity column i
n
#xxx)
SET ROWCOUNT @.NumToDelete
DELETE FROM dupedtable
WHERE Field1 = @.Field1 AND Field2 = @.Field2
That's about it.
Marc
"shiva" wrote:

> i have table and in that i have number of records which are duplicated.
> i want to delete the duplicate records from this table.
> pls help me.
>|||Marc L. Allen wrote:
> Unfrotunately, I have not found a clean way to do this. Uri's method will
> work great, but technically, his table doesn't have duplicate records (as
the
> identity column prevents that).
> It you have true duplicates, here's what I do:
> 1) Create a table containing all the table's fields plus a count field.
> CREATE TABLE #xxx
> (
> Field1 int,
> Field2 int,
> NumToDelete int
> )
> 2) Select the records which are duplicate:
> INSERT INTO #xxx
> SELECT *, COUNT(*) - 1
> FROM dupedtable
> GROUP BY Field1, Field2
> HAVING COUNT(*) > 1
> 3) Either loop or generate a cursor and go through each record in #xxx
> perfoming the following (note, if you loop, you'll need an identity column
in
> #xxx)
> SET ROWCOUNT @.NumToDelete
> DELETE FROM dupedtable
> WHERE Field1 = @.Field1 AND Field2 = @.Field2
> That's about it.
> Marc
>
> "shiva" wrote:
>
delete #demo from #demo inner join #Demo d on #demo.idno < d.idno and
#demo.cola = d.cola and #demo.colb = d.colb
Regards|||Here is the process to delete the duplicate records posted some days back by
some body.
1. SELECT * INTO #Temp1 FROM [Table1]
2. TRUNCATE TABLE [Table1]
3. CREATE UNIQUE INDEX [Index1] ON [Table1] (Unique Column Names) WITH
IGNORE_DUP_KEY
4. INSERT INTO [Table1] (Column Names) SELECT (Column Names) FROM [Table1]
5. DROP INDEX [Table1].[Index1]
Note: In 4 th step,it will take the first row in list of duplicates ,rest of
them will be ignored
Thanks
Kumar
"shiva" wrote:

> i have table and in that i have number of records which are duplicated.
> i want to delete the duplicate records from this table.
> pls help me.
>