Delete duplicate fields in a table

[Cloonalt]

I have a table with a field that has duplicate values. The rest of the record may not be unique.

So, for instance, if my table has two (or more) values of Mary in a field called FirstName, I want to leave only one of them, even if the address is not the same. (This is just an example)
It doesn't matter which ones get deleted. Can I do that in a SQL statement?

Thanks.

 

[tanman1975]

are you creating a new table with unique values or are you wanting to remove the excell records with duplicate field values?

 

[Cloonalt]

It's a SQL table and I want to remove the records with the duplciate field values.

Thanks.

 

[tanman1975]

does the table have a primary key?

That would make it easy. You could do something like this:

DELETE your_table
FROM (SELECT firstname, MIN(primaryKey) AS taxpayer_id
FROM your_table
GROUP BY firstname) foo RIGHT OUTER JOIN
your_table ON foo.primaryKey = your_table.primaryKey
WHERE (foo.firstname IS NULL)

 

[williamkremer]

If your table is not too large, you may want to view the duplicate entries first before deleting:

select count(*) as Dupes, FieldWithDupes, OneOtherField from My_table
group by FieldWithDupes, OneOtherField having count(*) > 1
order by FieldWithDupes, OneOtherField

"You cannot hold a torch to another man's path without brightening your own"
Best regards,
Bill

 

[Geoka]

Try this sample

[COLOR=red]
Insert Into #Test
Select 100, 10001, '20060301', 300, 2550.00, 125.00 Union All
Select 101, 10003, '20060301', 300, 3050.00, 652.00 Union All
Select 102, 10001, '20060302', 300, 2680.00, 410.00 Union All
Select 103, 10002, '20060302', 300, 5620.00, 350.00 Union All
Select 104, 10002, '20060303', 300, 7564.00, 124.00 Union All
Select 105, 10001, '20060303', 300, 3265.00, 451.00 Union All
Select 106, 10003, '20060303', 300, 9856.00, 00.00 
[/color]
[COLOR=blue]
Set RowCount  1
Select CustomerID, Count(CustomerID) From #Test
	Group by CustomerID Having Count(CustomerID)>1
While @@RowCount > 0
Delete from #Test Where CustomerID in (Select CustomerID From #Test
	Group by CustomerID Having Count(CustomerID)>1)
Set RowCount 0
[/color]

 

[Geoka]

Oops Small Correction in the above. Forgot to include the code for creating the #Test. Here goes the correct one

[COLOR=red]
Create Table #Test( ID Int, CustomerID Int, InvoiceDate DateTime, VechicleID Int,
	InvoiceAmt Numeric(12,2), Salestax Numeric(12,2))
Insert Into #Test
Select 100, 10001, '20060301', 300, 2550.00, 125.00 Union All
Select 101, 10003, '20060301', 300, 3050.00, 652.00 Union All
Select 102, 10001, '20060302', 300, 2680.00, 410.00 Union All
Select 103, 10002, '20060302', 300, 5620.00, 350.00 Union All
Select 104, 10002, '20060303', 300, 7564.00, 124.00 Union All
Select 105, 10001, '20060303', 300, 3265.00, 451.00 Union All
Select 106, 10003, '20060303', 300, 9856.00, 00.00 
[/color]
[COLOR=blue]
Set RowCount  1
Select CustomerID, Count(CustomerID) From #Test
	Group by CustomerID Having Count(CustomerID)>1
While @@RowCount > 0
Delete from #Test Where CustomerID in (Select CustomerID From #Test
	Group by CustomerID Having Count(CustomerID)>1)
Set RowCount 0
select * from #Test
[/color]