Removing duplicated rows?

[BittenApple]

Hello team,

I need to remove duplicated rows from a data set.
This is what I found when I searched it in Google:
delete from names a where rowid > (select min(rowid) from names b where b.name=a.name);

Why does it say min in this statement?

select min(rowid) from names b where b.name=a.name

Please advise me.

Thanks,
BittenApple

[burrina]

Why not use the duplicates query?

[BittenApple]

burrina,
Thanks,
I ran duplicate query and it worked but how should I delete the duplicated records and keep only one?

I wrote this query:
SELECT First(Table1.Field1) AS [Field1 Field], First(Table1.Field2) AS [Field2 Field], First(Table1.Field3) AS [Field3 Field], First(Table1.Field4) AS [Field4 Field], Count(Table1.Field1) AS NumberOfDups
FROM Table1
GROUP BY Table1.Field1, Table1.Field2, Table1.Field3, Table1.Field4
HAVING (((Count(Table1.Field1))>1) AND ((Count(Table1.Field4))>1));

This query shows duplicated record/ record.

How to delete the duplicated records?
Regards,
BittenApple

[burrina]

If you know which record you want to keep, just filter the query. I would NOT delete ANY records if it were me, I would simply archive them, but up to you.
As always BACK UP your Database before you do anything to it.
Preferred way would be to make a temp table, i.e copy of the original table but structure ONLY. Set it to No duplicates for your criteria then run an append query.
HTH

[BittenApple]

Hello burrina,
I have 1000,000 rows. Filtering them is very time consuming.
Thanks,
BittenApple

[BittenApple]

burrina,
I was able to delete duplicated records by grouping and having count>1 on only one field and then change select query to delete query, it worked. But I am looking for a simple way.
Regards,
BittenApple

[HansV]

You could do the following:
- Copy the table in the navigation pane, then paste it.

S0148.png

- Select the option to paste the structure only.
- You now have an empty copy of the table.
- Create a Totals (GROUP BY) query based on the original table to return unique records.
- Change it to an Append query with the new table as target, and run it.
- The new table now has unique records only.

[BittenApple]

Thanks,