delete from duplicate records
-
i am finding duplicates like this
SELECT ChstoragelocationCode
FROM StorageLocation
GROUP BY ChstoragelocationCode
HAVING COUNT(ChstoragelocationCode) > 1
order by ChstoragelocationCode
now i want to delete all duplicates of particular record and want to keep only one form that duplicate records
suppose for "ChstoragelocationCode" i am getting 4 records so i want only one out of 4
Plz suggest
-
If you have an identity field or some unqiue integer field like StorageLocationID - unique id .... the below will work
drop table #StorageLocation
go
create table #StorageLocation ( StorageLocationID int identity(1,1) , ChstoragelocationCode varchar(30))
go
insert #StorageLocation select 'SBC'
insert #StorageLocation select 'SBC'
insert #StorageLocation select 'SBC'
go
SELECT * FROM #StorageLocation
go
DELETE sto
FROM #StorageLocation sto
WHERE EXISTS ( SELECT *
FROM #StorageLocation
WHERE ChstoragelocationCode = sto.ChstoragelocationCode
AND StorageLocationID > sto.StorageLocationID)
go
SELECT * FROM #StorageLocation
go
-
The following articles will tell you how to do it
http://www.sqlteam.com/item.asp?ItemID=3331
http://www.sql-server-performance.com/rd_delete_duplicates.asp
There is also an article on technet about how to do this
Dave
-
Thought AH did a pretty good job!
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply