Deleting Duplicate Rows in a MySQL Database

Question

I've the following database

my_table [id,name,address,phone] with a lot of entries and i would like to delete the Duplicate data where any just any duplicated phone will results in deleting.

Here is my try but shows error

Inside my sql file

CREATE TABLE `my_table` ( `id` int(10) NOT NULL default '0', `name` varchar(255) NOT NULL default '', `address` varchar(255) NOT NULL default '', `phone` varchar(255) NOT NULL default '', PRIMARY KEY (`id`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1; INSERT INTO `my_table` VALUES (1, 'Albert', 'EGYPT', '202020'); INSERT INTO `my_table` VALUES (2, 'John', 'USA', '984731'); INSERT INTO `my_table` VALUES (3, 'Steve', 'Romabia', '202020'); INSERT INTO `my_table` VALUES (4, 'Albert', 'EGYPT', '343354');

very clear that row of id 1 and 3 have same phone number then will remove duplicated so results be

INSERT INTO `my_table` VALUES (1, 'Albert', 'EGYPT', '202020'); INSERT INTO `my_table` VALUES (2, 'John', 'USA', '984731'); INSERT INTO `my_table` VALUES (3, 'Albert', 'EGYPT', '343354');

How is my try

I just have added to the sql file the following

1- created new table to get distinct

CREATE TABLE my_temp(id VARCHAR(10), name VARCHAR(255), address VARCHAR(255), phone VARCHAR(255)); INSERT INTO my_temp(id,name,address,phone) SELECT DISTINCT id,name,address,phone FROM my_table;

2- Delete entries from real table my_table

DELETE FROM my_table;

3- Getting entries back from my_tamp table to real my_table

INSERT INTO my_table(id,name,address,phone) SELECT id,name,address,phone FROM my_temp;

4- Drop useless table my_temp

DROP TABLE my_temp;

Now my problem

it will still show me the same

INSERT INTO `my_table` VALUES (1, 'Albert', 'EGYPT', '202020'); INSERT INTO `my_table` VALUES (2, 'John', 'USA', '984731'); INSERT INTO `my_table` VALUES (3, 'Steve', 'Romabia', '202020'); INSERT INTO `my_table` VALUES (4, 'Albert', 'EGYPT', '343354');

because it won't consider no duplicate since they differ in id,name,address

so how i can adjust my way so that it delete duplicate if any only if there is duplicate in phone without care of id,name,address if differ or not

Hint

i've adjust this part

INSERT INTO my_temp(id,name,address,phone) SELECT DISTINCT phone FROM my_table;

but it will insert into my_temp table

INSERT INTO `my_table` VALUES (1, 'null', 'null', '202020'); INSERT INTO `my_table` VALUES (2, 'null', 'null', '984731'); INSERT INTO `my_table` VALUES (3, 'null', 'null', '343354');

so i won't be able to get the data back to my_table

GordonM · Accepted Answer · 2011-12-30 11:29:56Z

I'd do it the following way:

Create a temporary table from your existing table:

CREATE TEMPORARY TABLE data_to_keep LIKE table_with_dupes_in_it

Populate the temp table with just the records you want:

INSERT INTO data_to_keep SELECT DISTINCT * FROM table_with_dupes_in_it

Empty the table
```
TRUNCATE TABLE table_with_dupes_in_it 
```

Return the data from the temp table to the original table

INSERT INTO table_with_dupes_in_it SELECT * FROM data_to_keep;

Clean up
```
DROP TEMPORARY TABLE data_to_keep 
```

Be advised that this can eat up a huge amount of memory and/or storage if the table in question is a big one. If it's a big table I'd be inclined to use a real table instead of a temp table so as not to eat up excessive amounts of memory on your DB server.

EDIT TO ADD:

If you're just worried about partial dupes (rows where only some of the data is identical to previously entered data) then you will want to use GROUP BY. When you use GROUP BY, you can limit MySQL to return only one row that contains given data instead of all of them.

SELECT * FROM table GROUP BY column_name

You also should considering using UNIQUE indexes on the columns you want to not hold duplicate data, this will prevent users from inserting duplicate data in the first place.

~ thanks but it only delete duplicates if the whole row is duplicated while i want to remove the row if only the 'phone' duplicated.
~ Your edit note was very helpful to my and saved a lot of my time , thanks a lot now it works perfect.
Maybe you should a unique key on phone to prevent further occurrences?

Grim... · Accepted Answer · 2011-12-30 11:33:42Z

SELECT * FROM my_table t1 INNER JOIN my_table t2 ON t2.phone = t1.phone WHERE t1.id < t2.id

Switch SELECT * to DELETE t2.* once you've tested it (or DELETE t1.* if you'd rather keep the newer entries).

Collectives™ on Stack Overflow

Deleting Duplicate Rows in a MySQL Database

2 Answers 2

4 Comments

Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

4 Comments

Comments

Linked

Related