CODEWITHSUNDEEP
mysqlduplicates
Philipp Braun
Philipp Braun

Reputation: 1573

Remove duplicates from Mysql Table

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

Upvotes: 1

Views: 98

Answers (2)

Joachim Isaksson
Joachim Isaksson

Reputation: 180877

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.

Upvotes: 3

cidms
cidms

Reputation: 11

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

Upvotes: 1

Related Questions