Create a gist now

Instantly share code, notes, and snippets.

What would you like to do?
MySQL: find likely duplicates. SELECT first, then DELETE for safety. Alternatively, delete directly.
# Delete directly:
# -------------------------------------
DELETE
n1
FROM
`tableName` n1, `tableName` n2
WHERE
n1.id = n2.id AND
n1.lastmodifieddate = n2.lastmodifieddate AND
n1.name = n2.name AND
n1.uid > n2.uid AND
n1.uid <> n2.uid
# Select first (recommended):
# -------------------------------------
SELECT
n1.uid
FROM
`tableName` n1, `tableName` n2
WHERE
n1.id = n2.id AND
n1.lastmodifieddate = n2.lastmodifieddate AND
n1.name = n2.name AND
n1.uid > n2.uid AND
n1.uid <> n2.uid
# ---------
# And then this:
DELETE
FROM
`tableName`
WHERE
uid IN ()
Owner

paulrohrbeck commented Sep 5, 2014

"uid" in this case is the actual unique index within the table. "id" is an id that could be duplicated for various reasons.

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment