How to delete duplicate rows

Hi,
Currently we are having one large table in that we are having more than
1 lacs record but most of the records are duplicates(All columns are having
same values).
How can i delete the particular duplicate row?
Please give me a solution as soon as possible
Thanks,
Herbert
Hi
This script has written by Itzik Ben-Gan
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
"Herbert" <Herbert@.discussions.microsoft.com> wrote in message
news:BE1F27AD-B81D-4CF6-94B7-630BC7979FF0@.microsoft.com...
> Hi,
> Currently we are having one large table in that we are having more
than
> 1 lacs record but most of the records are duplicates(All columns are
having
> same values).
> How can i delete the particular duplicate row?
> Please give me a solution as soon as possible
> Thanks,
> Herbert
|||delete tablename
WHERE (((tablename.dupfield) In (SELECT dupfield FROM tablename As Tmp GROUP
BY dupfield HAVING Count(*)>1 )))
"Herbert" wrote:

> Hi,
> Currently we are having one large table in that we are having more than
> 1 lacs record but most of the records are duplicates(All columns are having
> same values).
> How can i delete the particular duplicate row?
> Please give me a solution as soon as possible
> Thanks,
> Herbert
|||INF: How to Remove Duplicate Rows From a Table
http://support.microsoft.com/default...44&Product=sql
AMB
"Herbert" wrote:

> Hi,
> Currently we are having one large table in that we are having more than
> 1 lacs record but most of the records are duplicates(All columns are having
> same values).
> How can i delete the particular duplicate row?
> Please give me a solution as soon as possible
> Thanks,
> Herbert
|||Hi,
Suppose if the table doesn't have any identity column and all the
remaining columns are equal and i want to have only one row and delete all
duplicate rows.
thanks.,
herbert
"Uri Dimant" wrote:

> Hi
> This script has written by Itzik Ben-Gan
> 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
> "Herbert" <Herbert@.discussions.microsoft.com> wrote in message
> news:BE1F27AD-B81D-4CF6-94B7-630BC7979FF0@.microsoft.com...
> than
> having
>
>