Go Back   TechArena Community > Software > Software Development
Become a Member!
Forgot your username/password?
Tags Active Topics RSS Search Mark Forums Read

Reply
 
Thread Tools Search this Thread
  #1  
Old 18-03-2009
Member
 
Join Date: Feb 2009
Posts: 69
How to Remove Duplicate Entries from Table ?

There are certain entries in the database table that already exists. These entries are added more than once for some reason. How do I remove/delete these duplicate entries from the database table ?
Reply With Quote
  #2  
Old 18-03-2009
Member
 
Join Date: Apr 2008
Posts: 2,001
Re: How to Remove Duplicate Entries from Table ?

This is an extremely quick and painless way to remove duplicate rows (tuples) from a MySQL database table. The best part of it is that it requires no programming or PHP coding whatsoever, it can all be done with three manual SQL queries :

Step 1: move the non duplicates (unique tuples) into a temporary table
CREATE TABLE new_table AS
SELECT * FROM old_table WHERE 1 GROUP BY [COLUMN TO remove duplicates BY];

Step 2: delete delete the old table
We no longer need the table with all the duplicate entries, so drop it!
DROP TABLE old_table;

Step 3: rename the new_table to the name of the old_table
RENAME TABLE new_table TO old_table;
Reply With Quote
  #3  
Old 18-03-2009
Member
 
Join Date: May 2008
Posts: 2,008
Re: How to Remove Duplicate Entries from Table ?

Before removing duplicate entries from the database table, first you need to find the duplicates(duplicate entries).

There is a handy query for finding duplicates in a table.
Suppose you want to find all email addresses in a table that exist more than once:
SELECT email,
COUNT(email) AS NumOccurrences
FROM users
GROUP BY email
HAVING ( COUNT(email) > 1 )


You could also use this technique to find rows that occur exactly once:
SELECT email
FROM users
GROUP BY email
HAVING ( COUNT(email) = 1 )
Reply With Quote
  #4  
Old 18-03-2009
Member
 
Join Date: Apr 2008
Posts: 1,948
Re: How to Remove Duplicate Entries from Table ?

Apart from using primary key of the table and putting queries using primary key to remove duplicate entries from table, there is another approach that you can use by creating a second table with a primary key or unique constraint on that column, and insert into the new table all the rows from the old table.
For each unique value, the first insert will succeed but all subsequents will fail due to violation of key or unique constraint.

Once you are done, you can rename the old table to something else, and rename the new table with the old name.
Reply With Quote
Reply

  TechArena Community > Software > Software Development
Tags: ,



Thread Tools Search this Thread
Search this Thread:

Advanced Search


Similar Threads for: "How to Remove Duplicate Entries from Table ?"
Thread Thread Starter Forum Replies Last Post
Is it possible that Pivot Table can list duplicate entries in Microsoft Excel Sachi Trivedi MS Office Support 5 24-01-2012 05:02 PM
Removing duplicate entries from an array in VB McKenzie! Software Development 6 27-07-2011 11:47 AM
Duplicate Calendar Entries ksharkman Windows Software 2 08-04-2011 12:36 AM
Delete the entries of Table 1 according to criterion of other table Wguy2008 Software Development 2 24-04-2009 11:13 PM
Scavenging or something else to cleanup duplicate DNS entries? User Name Windows Server Help 4 06-10-2008 07:36 PM


All times are GMT +5.5. The time now is 02:51 AM.