How to Determine the unique IDs of duplicated records

> This is a common problem with some solution

/************************************************** *********************************
*
* Problem:
* Determine the Duplicated Records in a table using single SELECT.
*
* We shall be using Northwind database, add some duplicate records.
*
* Here we want to know if 2 columns (CompanyName,
* PHone) are duplicated in a table.
*
*
* ShipperID CompanyName Phone
* ---- -------- ------
* 1 Speedy Express (503) 555-9831
* 2 United Package (503) 555-3199
* 3 Federal Shipping (503) 555-9931
* 4 Federal Shipping (503) 555-9931
* 5 Speedy Express (503) 555-9831
* 6 Federal Shipping (503) 555-9931
*
*
*
************************************************** **/

==================================================

SOLUTION 1: Gives me the IDs that are duplicated.

==================================================

SELECT
ShipperID, CompanyName, Phone
FROM
SHIPPERS
WHERE
EXISTS (
SELECT
NULL
FROM
SHIPPERS b
WHERE
b.CompanyName = SHIPPERS.CompanyName
AND b.Phone = SHIPPERS.Phone
GROUP BY
b.CompanyName, b.Phone
HAVING
SHIPPERS.ShipperID < MAX( b.ShipperID )
)

/* ********************
* Output results
********************/

ShipperID CompanyName Phone

---- ------------
--------
1 Speedy Express (503) 555-9831
3 Federal Shipping (503) 555-9931
4 Federal Shipping (503) 555-9931

(3 row(s) affected)

================================================== ===========

SOLUTION 2: Gives me the data which are duplicate but
not the IDs

================================================== ===========

SELECT
CompanyName, Phone
FROM
SHIPPERS
GROUP BY
CompanyName, Phone
HAVING
COUNT(*) > 1

/* ********************
* Output results
********************/

CompanyName Phone
------------ --------
Speedy Express (503) 555-9831
Federal Shipping (503) 555-9931

(2 row(s) affected)anonieko@.hotmail.com wrote:
> > This is a common problem with some solution
> /************************************************** *********************************
> *
> * Problem:
> * Determine the Duplicated Records in a table using single SELECT.
> *
> * We shall be using Northwind database, add some duplicate records.
> *
> * Here we want to know if 2 columns (CompanyName,
> * PHone) are duplicated in a table.
> *
> *
> * ShipperID CompanyName Phone
> * ---- -------- ------
> * 1 Speedy Express (503) 555-9831
> * 2 United Package (503) 555-3199
> * 3 Federal Shipping (503) 555-9931
> * 4 Federal Shipping (503) 555-9931
> * 5 Speedy Express (503) 555-9831
> * 6 Federal Shipping (503) 555-9931
> *
> *
> *
> ************************************************** **/
> ==================================================
> SOLUTION 1: Gives me the IDs that are duplicated.
> ==================================================
> SELECT
> ShipperID, CompanyName, Phone
> FROM
> SHIPPERS
> WHERE
> EXISTS (
> SELECT
> NULL
> FROM
> SHIPPERS b
> WHERE
> b.CompanyName = SHIPPERS.CompanyName
> AND b.Phone = SHIPPERS.Phone
> GROUP BY
> b.CompanyName, b.Phone
> HAVING
> SHIPPERS.ShipperID < MAX( b.ShipperID )
> )
> /* ********************
> * Output results
> ********************/
> ShipperID CompanyName Phone
> ---- ------------
> --------
> 1 Speedy Express (503) 555-9831
> 3 Federal Shipping (503) 555-9931
> 4 Federal Shipping (503) 555-9931
> (3 row(s) affected)
>
> ================================================== ===========
> SOLUTION 2: Gives me the data which are duplicate but
> not the IDs
> ================================================== ===========
>
> SELECT
> CompanyName, Phone
> FROM
> SHIPPERS
> GROUP BY
> CompanyName, Phone
> HAVING
> COUNT(*) > 1
>
> /* ********************
> * Output results
> ********************/
>
> CompanyName Phone
> ------------ --------
> Speedy Express (503) 555-9831
> Federal Shipping (503) 555-9931
> (2 row(s) affected)

Those aren't solutions, they are diagnostics. The solution is to fix
the stupid design of the Shippers table by adding a proper key.

:-)

--
David Portas, SQL Server MVP

Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.

SQL Server Books Online:
http://msdn2.microsoft.com/library/...US,SQL.90).aspx
--sql

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.
>