MySQL remove duplicate rows

Suppose you have tablename with id, column1 and column2 (and other columns of course). But you have lots of duplicates. You know you need to remove them based on uniqueness column1 and column2. So you fixed script that creates duplicates, now you need to remove the existing duplicates:

1. Backup your table
2. You can preview duplicate rows replacing DELETE t1 with select *
3. Run following MySQL command

MySQL

DELETE t1 FROM tablename t1
JOIN tablename t2 ON t1.column1 = t2.column1 AND t1.column2 = t2.column2
WHERE t1.id > t2.id;

PostgreSQL

DELETE t1
FROM tablename t1
JOIN tablename t2 ON t1.column1 = t2.column1 AND t1.column2 = t2.column2
WHERE t1.id > t2.id;

Warning

If you have big table, use your scripting language and run it one by one, for example in php. This is done to not lock your table for a lot of time. So we will del one by one.

$sql = "SELECT DISTINCT(column1) FROM tablename WHERE 1 ORDER BY person_id";
$stmt = $databaseWrapper->run($sql);
while ($column1 = $stmt->fetchColumn()) {

$databaseWrapper->run("
DELETE t1 FROM tablename t1
JOIN tablename t2 ON t1.column1 = t2.column1 AND t1.column2 = t2.column2
WHERE t1.column1 = {$column1} t1.id > t2.id
");
}