How to delete duplicate records in MYSQL with PHP.

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
dmacman1962
Forum Newbie
Posts: 14
Joined: Wed Mar 10, 2004 2:58 pm

How to delete duplicate records in MYSQL with PHP.

Post by dmacman1962 »

Hi everyone,

I have a simple MYSQL table with about 4000 records. The only field is category. The data is category types, IE: restaurants, hairdressers, car dealers, etc. There are alot of duplicates and a lot of unique entries.

I have searched the web and here for about 2 hours on a simple way to either delete the duplicates, and leave an original. Or, create a new table from the existing one and only get unique entries.

I don't know quite how to do this. Any direction would be greatly appreciated.

Thanks in advance,

Dmacman
Last edited by dmacman1962 on Tue Mar 16, 2004 3:11 pm, edited 1 time in total.
TheBentinel.com
Forum Contributor
Posts: 282
Joined: Wed Mar 10, 2004 1:52 pm
Location: Columbus, Ohio

Re: How to delete duplicate records.

Post by TheBentinel.com »

dmacman1962 wrote:Hi everyone,

I have a simple table with about 4000 records. The only field is category. The data is category types, IE: restaurants, hairdressers, car dealers, etc. There are alot of duplicates and a lot of unique entries.

I have searched the web and here for about 2 hours on a simple way to either delete the duplicates, and leave an original. Or, create a new table from the existing one and only get unique entries.

I don't know quite how to do this. Any direction would be greatly appreciated.

Thanks in advance,

Dmacman
There's a DISTINCT clause in SQL, does your database support it?

SELECT DISTINCT * FROM YourTable

It will return only the unique entries.

Hope it helps!
User avatar
JAM
DevNet Resident
Posts: 2101
Joined: Fri Aug 08, 2003 6:53 pm
Location: Sweden
Contact:

Post by JAM »

As mentioned, 'select distinct ...' would make only, well, distinct searches.

If you have an issue with the multible data you could dublicate the table structure then do a:

Code: Select all

insert into newtable
select distinct * from oldtable
...and voilá, a fresh start.
Post Reply