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 :)

What is your criteria for a duplicate? Duplicate addresses, or all fields except id or...? — Joachim Isaksson
– Joachim Isaksson, Commented May 19, 2014 at 7: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
– Philipp Braun, Commented May 19, 2014 at 7:05

Joachim Isaksson · Accepted Answer · 2014-05-19 07:12:25Z

3

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.

answered May 19, 2014 at 7:12

Joachim Isaksson

182k28 gold badges297 silver badges307 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Community · Accepted Answer · 2017-05-23 12:15:39Z

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, 2017 at 12:15

CommunityBot

11 silver badge

answered May 19, 2014 at 7:09

cidms

113 bronze badges

Collectives™ on Stack Overflow

Remove duplicates from Mysql Table

2 Answers 2

Comments

Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Linked

Related