|
|
![]() |
| Thread Tools | Search this Thread |
#1
| |||
| |||
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 ? |
#2
| |||
| |||
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; |
#3
| |||
| |||
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 ) |
#4
| |||
| |||
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. |
![]() |
|
Tags: duplicate entries, remove |
Thread Tools | Search this Thread |
|
![]() | ||||
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 |