Chris Thompson cjthompson

Primary a back-end JavaScript/node.js developer. Founder of Osiris Development, a contracting and consulting firm for node.js development.

cjthompson / DeleteDuplicates.sql

Created March 20, 2013 17:43

Find duplicate records in a MySQL database, save the first row of a set of duplicates, and delete the rest.

	-- Assume `Table` has 4 columns: id, col1, col2, col3

	CREATE TABLE `dups` (
	`id` int(10) unsigned DEFAULT NULL,
	`hash` varbinary(32) DEFAULT NULL,
	KEY `id` (`id`),
	KEY `hash` (`hash`)
	) ENGINE=InnoDB DEFAULT CHARSET=latin1;

	INSERT INTO dups SELECT MIN(id) AS id, MD5(CONCAT(col1, col2, col3)) AS hash FROM `Table` GROUP BY col1, col2, col3 HAVING COUNT(*) > 1;