Skip to content

Instantly share code, notes, and snippets.

@paulrohrbeck
Created September 5, 2014 18:52
Show Gist options
  • Save paulrohrbeck/b0b7e42bc7d8ea6d6563 to your computer and use it in GitHub Desktop.
Save paulrohrbeck/b0b7e42bc7d8ea6d6563 to your computer and use it in GitHub Desktop.
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 ()
@paulrohrbeck
Copy link
Author

"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