duplicates

How do I delete duplicate entries?
For example, lets says I have the following table format:
SystemName, Memory, CPU
I would like to delete any duplicates that are in SystemName.See if this helps:
http://support.microsoft.com/defaul...kb;en-us;139444
--
HTH,
Vyas, MVP (SQL Server)
SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
"Big D" <BigDaddy@.newsgroup.nospam> wrote in message
news:O8KImBgYFHA.2948@.TK2MSFTNGP10.phx.gbl...
How do I delete duplicate entries?
For example, lets says I have the following table format:
SystemName, Memory, CPU
I would like to delete any duplicates that are in SystemName.|||Please refer to www.aspfaq.com/5006 and provide sufficient information for
others to better understand you problem.
Deletes are applicable to a set of rows, not individual columns. If the
entire row is duplicated, then use the link posted by Vyas & make sure you
explicitly declare a primary key to avoid further duplication. If only the
sytemname is duplicated, they you will have to decide which values of memory
and/or cpu should be preserved and which ones should be deleted. Generally,
one can apply an extrema aggregate like MIN or MAX like:
SELECT *
FROM tbl t1
WHERE t1.Memory = ( SELECT MAX( t2.memory ) FROM tbl t2
WHERE t2.system = t1.system )
You can convert this into a delete like:
DELETE FROM tbl
WHERE Memory = ( SELECT MAX( t1.memory ) FROM tbl t1
WHERE tbl.system = t1.system )
If the duplicated values are determined by memory and/or cpu, you can use
WHERE Memory + '!' + cpu = ( SELECT MAX( t1.Memory + '!' + t1.cpu ) ...
Another way of writing it would be
DELETE FROM tbl
WHERE EXISTS ( SELECT * FROM tbl t1
WHERE t1.Memory > tbl.Memory
OR t1.cpu > tbl.cpu
OR ... )
Anith|||Of course, once you have duplicates purged, you should implement a unique
key constraint to prevent this from occurring again. But to answer your
question, let's say you have a table called Accounts and the column
combination tran_type should be unique. The following query will list all
records that violate the non-unique rule. We are basically joining back to a
group by sub-query having count(*) > 1. You can modify this as needed to fit
your situation. Once done, you can better profile the records to determine
which should be deleted. For example, do you want to keep the first entered
or last entered, or perhaps duplicates entered during a specific time frame
should be deleted.
select
Accounts.*
from
Accounts
join
(
select
account,
tran_type,
count(*) as cnt
from
Accounts
group by
account,
tran_type
having
count(*) > 1
) as x
on x.account= Accounts.account and
x.tran_type= Accounts.tran_type
order by
NSA.account,
NSA.tran_type
"Big D" <BigDaddy@.newsgroup.nospam> wrote in message
news:O8KImBgYFHA.2948@.TK2MSFTNGP10.phx.gbl...
> How do I delete duplicate entries?
> For example, lets says I have the following table format:
> SystemName, Memory, CPU
> I would like to delete any duplicates that are in SystemName.
>
>