Deleting duplicates with group by and count

What is the fastest method to convert the following query:

SELECT COUNT(*) as c FROM tbl_fields
WHERE fieldnotes IS NULL
GROUP BY fieldno,fieldserial,id,fielddate,fieldsid 
HAVING COUNT(*) > 1;

... into one that will delete duplicated records? The table does not have any primary keys and contains several million entries.

According to your query, you have fieldno,fieldserial,id,fielddate,fieldsid as a rule for uniqueness in the GROUP BY clause.

You can try this :

CREATE TABLE tbl_fields_unique LIKE tbl_fields;
ALTER TABLE tbl_fields_unique
ADD UNIQUE KEY unq (fieldno,fieldserial,id,fielddate,fieldsid);
INSERT IGNORE INTO tbl_fields_unique
SELECT * FROM tbl_fields;
ALTER TABLE tbl_fields RENAME tbl_fields_old;
ALTER TABLE tbl_fields_unique RENAME tbl_fields;

This will filter rows with duplicate fieldno,fieldserial,id,fielddate,fieldsid fields. Look over the new table. Once you are satisfied with the contents of the tbl_fields_unique table, do this:

ALTER TABLE tbl_fields RENAME tbl_fields_old;
ALTER TABLE tbl_fields_unique RENAME tbl_fields;

Give it a Try !!!

StackExchange Database Administrators Q#9602, answer score: 3