Deleting Repeating Rows

[74Stag]

Hi

I need to create a query to delete repeating rows in a table. I need to keep only the rows which do not have any duplicate entries.

For example if I had the following entries:

A
A
B
C
D
D

I need to be left with just:

B
C

Can anyone offer any ideas?
Thanks.

 

[r937]

select thecolumn
into newtable
from thetable
group
by thecolumn
having count(*) = 1

rudy | r937.com | Ask the Expert | Premium SQL Articles
SQL for Database-Driven Web Sites (next course starts March 6 2005)

 

[lupins46]

Use the Find Duplicates query wizard.
Create the query to list the duplicates.
Change the query type to a Delete query.

Make sure you have a backup.

 

[PHV]

Create a saved maketable query named, say, qryGetDup:
SELECT [yourDupFieldName] INTO tblDup
FROM [yourTable]
GROUP BY [yourDupFieldName]
HAVING Count(*)>1;

Then the purge query:
DELETE FROM [yourTable] INNER JOIN tblDup
ON [yourTable].[yourDupFieldName]=tblDup.[yourDupFieldName];

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[74Stag]

Thanks for the quick response everyone. They all worked great!