getting rid of duplicates
rgu,
While this might not be the most efficient way to delete duplicates,
here's a sneaky way to do it put temporarily putting a trigger on the table.
It uses the deleted table in exactly the same way you might use a temporary
table, but technically you aren't creating a new table.
--Create a sample table with some identical rows
select orderid, productid into SomeDups from Northwind..[Order Details]
insert into SomeDups
select * from SomeDups where orderId%7=2
insert into SomeDups
select * from SomeDups where orderId%5=2
go
create clustered index SomeDups_op on SomeDups(orderid,productid)
go
--Check how many rows and distinct rows
select count(*) as Rows from SomeDups
select count(*) as [Distinct Rows] from (
select distinct * from SomeDups
) X
go
--add the sneaky trigger!
create trigger SomeDups_d
on SomeDups for delete as
insert into SomeDups
select distinct * from deleted
go
--delete ALL the duplicated rows
--and the trigger magically adds back one of each
delete from SomeDups
from SomeDups A
where (
select count(*) from SomeDups B
where a.orderid = b.orderid
and a.productid = b.productid
) > 1
go
--drop the trigger
drop trigger SomeDups_d
go
--Check the result
select count(*) as Rows from SomeDups
go
--Drop the sample table
drop table SomeDups
Steve Kass
Drew University
Quote:
> Hi there,
> Is there a way to get rid of duplicates with just one sql statement and not
> using a temporary table?
