Delete Repeating Rows - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Delete Repeating Rows

Author

Topic

oahu9872
Posting Yak Master

112 Posts

Posted - 2006-07-21 : 12:38:55

I have a table that has two identical entries for each customer. I need to go through and delete one of the two records. Is there a simple and easy way to do that.

Thanks

Srinika
Master Smack Fu Yak Hacker

1378 Posts

Posted - 2006-07-21 : 13:06:34

http://support.microsoft.com/default.aspx?scid=kb;en-us;139444
http://www.sqlteam.com/item.asp?ItemID=3331

Srinika

sqldba2k6
Posting Yak Master

176 Posts

Posted - 2006-07-21 : 13:07:10

I hope below script should help you.

/**********************************************
Example of a simple duplicate data delete script.
**********************************************/

/**********************************************
Set up test environment
**********************************************/
SET NOCOUNT ON

--Create test table
IF OBJECT_ID('tDupData') IS NOT NULL
DROP TABLE tDupData
GO

CREATE TABLE tDupData
(
lngCompanyID INTEGER
,strCompanyName VARCHAR(20)
,strAddress VARCHAR(10)
,dtmModified DATETIME
)

--Create test data
INSERT INTO tDupData VALUES (1,'CompanyOne','Address1','01/15/2003')
INSERT INTO tDupData VALUES (2,'CompanyTwo','Address2','01/15/2003')
INSERT INTO tDupData VALUES (3,'CompanyThree','Address3','01/15/2003')
INSERT INTO tDupData VALUES (2,'CompanyTwo','Address','01/16/2003')
INSERT INTO tDupData VALUES (3,'CompanyThree','Address','01/16/2003')

-- Dup Data
INSERT INTO tDupData VALUES (1,'CompanyOne','Address1','01/15/2003')
GO

/**********************************************
Finish set up
**********************************************/

/**********************************************
Simple duplicate data
**********************************************/

--Create temp table to hold duplicate data
CREATE TABLE #tempduplicatedata
(
lngCompanyID INTEGER
,strCompanyName VARCHAR(20)
,strAddress VARCHAR(10)
,dtmModified DATETIME
)

--Identify and save dup data into temp table
INSERT INTO #tempduplicatedata
SELECT * FROM tDupData
GROUP BY lngCompanyID,strCompanyName,strAddress, dtmModified
HAVING COUNT(*) > 1

--Confirm number of dup rows
SELECT @@ROWCOUNT AS 'Number of Duplicate Rows'

--Delete dup from original table
DELETE FROM tDupData
FROM tDupData
INNER JOIN #tempduplicatedata
ON tDupData.lngCompanyID = #tempduplicatedata.lngCompanyID
AND tDupData.strCompanyName = #tempduplicatedata.strCompanyName
AND tDupData.strAddress = #tempduplicatedata.strAddress
AND tDupData.dtmModified = #tempduplicatedata.dtmModified

--Insert the delete data back
INSERT INTO tDupData
SELECT * FROM #tempduplicatedata

--Check for dup data.
SELECT * FROM tDupData
GROUP BY lngCompanyID,strCompanyName,strAddress,dtmModified
HAVING COUNT(*) > 1

--Check table
SELECT * FROM tDupData

--Drop temp table
DROP TABLE #tempduplicatedata

--drop test table
IF OBJECT_ID('tDupData') IS NOT NULL
DROP TABLE tDupData
GO

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-07-22 : 02:31:37

Also refer
http://sqlteam.com/forums/topic.asp?TOPIC_ID=6256

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources