How To Delete One Record for Duplicates

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

Transact-SQL (2000)

How To Delete One Record for Duplicates

Author

Topic

AsimKhaliq
Yak Posting Veteran

94 Posts

Posted - 2004-03-17 : 17:04:21

Hi

I have some duplicate record based on some conditions in my database and want to delete all of them except for 1

Create table Table1(Name varchar (10),
Address varchar (50),
city varchar (10),
state varchar (10),
Zip varchar (10),
Country)

Insert into table1('A','1234','a','CA','12523','USA')
Insert into table1('A','1234','a','CA','12523','USA')
Insert into table1('A','1234','a','CA','12523','USA')
Insert into table1('B','222','GFT','FL','2351','USA')
Insert into table1('B','222','GFT','FL','2351','USA')
Insert into table1('C','322','XXT','IL','2555','USA')
Duplicate based on Name,Address and zip
Now i want to delete one record from each duplicate so my result will be
A,1234,a,CA,12523,USA
A,222,GFT,FL,2351,USA
C,322,XXT,IL,2555,USA
Thnks

ChrisFretwell
Starting Member

43 Posts

Posted - 2004-03-17 : 17:18:48

If you have nothing on your table like an identity column, then the best way to do this is to create a second table, select the records into it using a group by. Rename/delete the old table, rename the new table to the name or your original table.

If you have another column on your table like and identity then you can delete all but the min/max of this value leaving only one record.

byrmol
Shed Building SQL Farmer

1591 Posts

Posted - 2004-03-17 : 17:35:53

Do you promise to put a Key on those columns after you have cleaned it?


set rowcount 1
select 1
WHILE @@RowCOUNT > 0
BEGIN
	delete t
	FROM Table1 t
	WHERE EXISTS
	(SELECT 1 From table1 X
	Where t.Name = x.name and t.address = X.address and t.zip = X.zip
	GROUP BY Name, Address, Zip
	Having COUNT(*) > 1)
END
set rowcount 0
Select * from Table1

DavidM

"An Ugg Boot is a generic Australian term that has been in use for nearly 100 hundred years. Now some coporate wanker has trademarked it.. "

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-03-17 : 17:57:57

Always check the SQLTeam articles to see if it has been covered already:

http://www.sqlteam.com/item.asp?ItemID=3331

Tara

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources