getting rid of duplicates 
Author Message
 getting rid of duplicates
Hi there,

Is there a way to get rid of duplicates with just one sql statement and not
using a temporary table?



Tue, 13 Jul 2004 00:32:36 GMT
 getting rid of duplicates

How often are you doing this that using a temporary table is such a bad
thing?

www.aspfaq.com


Quote:
> Hi there,

> Is there a way to get rid of duplicates with just one sql statement and
not
> using a temporary table?



Tue, 13 Jul 2004 00:46:04 GMT
 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?



Tue, 13 Jul 2004 00:49:23 GMT
 getting rid of duplicates
Hi,
Please take a look at this article which deals with
removal of duplicates. One of techniques used is a derived
table.
http://www.swynk.com/friends/boyle/removeduplicate.asp
Bye
Quote:
>-----Original Message-----
>Hi there,

>Is there a way to get rid of duplicates with just one sql
statement and not
>using a temporary table?

>.



Tue, 13 Jul 2004 02:32:57 GMT
 getting rid of duplicates
It's a one time thing, just curious if it could be done with one statement



Quote:
> How often are you doing this that using a temporary table is such a bad
> thing?

> www.aspfaq.com



> > Hi there,

> > Is there a way to get rid of duplicates with just one sql statement and
> not
> > using a temporary table?



Tue, 13 Jul 2004 02:44:22 GMT
 getting rid of duplicates
Hi,

You can also refer to this article documented in Microsoft Knowledge Base:

Q139444 INF: How to Remove Duplicate Rows From a Table
http://support.microsoft.com/support/kb/articles/q139/4/44.asp

Hope this helps!

--- Simon Liao

This posting is provided "AS IS" with no warranties, and confers no rights.



Fri, 16 Jul 2004 17:05:38 GMT
 
 [ 6 post ] 

 Relevant Pages 

1. GETTING RID OF DUPLICATE COLUMNS..

2. Getting rid of duplicate records

3. Getting Rid of Duplicate Data

4. getting rid of duplicates

5. Getting rid of duplicate records.

6. Getting rid of the IDENTITY property on a column withou getting rid of the column

7. I am getting duplicate records, I mean everything is duplicate even Access record number

8. Get rid of Duplicate Data

9. How to get rid of duplicated rows?

10. How get rid of duplicate rows ?

11. Help to get rid of Duplicate Records


 
Powered by phpBB® Forum Software