Is there a purely SQL way of getting rid of duplicates in a table?

HunterDX77M · #1 (**permalink**) 02-09-12, 15:44

I'm dealing with a table that has been having issues with duplicate rows. I want to remove every extra row in the table. (The duplication problems occur when someone else's script runs on the table, and we've yet to pin point the location of the bug.)

I can think of at least one way to do this with a combination of PHP and MySQL, but I was wondering if MySQL alone had a mechanism that could do this already.

r937 · #2 (**permalink**) 02-09-12, 16:00

yes, you are fortunate, mysql has a really neat mechanism...

Quote:

Originally Posted by da manual

IGNORE is a MySQL extension to standard SQL. It controls how ALTER TABLE works if there are duplicates on unique keys in the new table or if warnings occur when strict mode is enabled. If IGNORE is not specified, the copy is aborted and rolled back if duplicate-key errors occur. If IGNORE is specified, only the first row is used of rows with duplicates on a unique key. The other conflicting rows are deleted. Incorrect values are truncated to the closest matching acceptable value.
-- MySQL :: MySQL 5.0 Reference Manual :: 12.1.4 ALTER TABLE Syntax

HunterDX77M · #3 (**permalink**) 02-09-12, 16:16

Quote:

Originally Posted by r937

yes, you are fortunate, mysql has a really neat mechanism...

I see. Thanks.

Internet Services

Web Development

Digital Marketing

Consumer Tech