March 28, 2006 at 9:28 am
im trying to delete duplicate rows in a table. im deleteing them where the description is the same. is this possible in one little statement or is an sproc with variables needed. i know i can do it with variables and maby a cursor, but can it be done with a co-related sub query and could someone show me how
March 28, 2006 at 9:30 am
When the system finds a 'duplicate', what logic should be used to determine which of them to delete?
March 28, 2006 at 9:36 am
Assuming that you want to preserve the first entry when duplicates are encountered, you can do something like this:
set nocount on
go
declare @table table (rowid int identity(1,1), DESC_COL varchar(20))
insert into @table values ('abc')
insert into @table values ('abc')
insert into @table values ('xyz')
insert into @table values ('xyz')
insert into @table values ('tab')
--before the delete
select * from @table
rowid DESC_COL
----------- --------------------
1 abc
2 abc
3 xyz
4 xyz
5 tab
delete x
from @table x, @table y
where x.DESC_COL = y.DESC_COL
and x.rowid > y.rowid
--after the delete
select * from @table
rowid DESC_COL
----------- --------------------
1 abc
3 xyz
5 tab
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply