Remove duplicates from Mysql Table

Question

I have a mysql table similar to this one where I want to remove duplicates.

CREATE TABLE IF NOT EXISTS `map` (
  `id` int(10) NOT NULL AUTO_INCREMENT,
  `name` text NOT NULL,
  `address` text NOT NULL,
  `type` text NOT NULL,
  `lat` text NOT NULL,
  `lng` text NOT NULL,
  PRIMARY KEY (`id`)
);
INSERT INTO `map` (`id`, `name`, `address`, `type`, `lat`, `lng`) VALUES
(607, 'Vostok Station', 'Antarctica', 'establishment', '-82.8627519', '-135'),
(608, 'Vostok Station', 'Antarctica', 'establishment', '-82.8627519', '-135');

I have already tried something like:

SELECT COUNT(address) AS numUsers;
delete from map 
where id in 
(SELECT MAX(id) FROM TABLE WHERE address IN (SELECT address FROM TABLE 
GROUP BY address 
HAVING COUNT(*)>1));

Please don't be to harsh to me if I made any faults. I am just a newbie with almost no experience :)

Did you tries [this](http://stackoverflow.com/questions/4685173/delete-all-duplicate-rows-except-for-one-in-mysql) ? — Ravi Dhoriya ツ, May 19 '14 at 07:02
What is your criteria for a duplicate? Duplicate addresses, or all fields except id or...? — Joachim Isaksson, May 19 '14 at 07:04
address is the criteria, maybe i implemented the temporary stuff wrong. like i said i really don't have so much experience. — Philipp Braun, May 19 '14 at 07:05

score 3 · Accepted Answer · answered May 19 '14 at 07:12

As far as I can see, a simple DELETE JOIN will do it;

DELETE m1
FROM map m1
JOIN map m2
  ON m1.address = m2.address
 AND m1.id > m2.id;

An SQLfiddle to test with.

This will delete all rows where there exists a row with a lower id and the same address.

...and always remember, always back up before running potentially destructive SQL from random people on the Internet.

score 1 · Answer 2 · edited May 23 '17 at 12:15

1

You can use temp table and insert ignore into to achieve what you want ( this is one way and there are multiple ways).

Similar questions already has been asked on stackoverflow see: How to delete Duplicates in MySQL table

edited May 23 '17 at 12:15

Community

1
1

answered May 19 '14 at 07:09

cidms

9
3

Remove duplicates from Mysql Table

2 Answers2