3

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.

4
  • 1
    Is there a field that is at least unique?
    – Aaron
    Dec 22, 2011 at 16:00
  • Unfortunately not. There is an id field, but once upon a time, someone merged similar tables, so there's no guarantee they are unique any more (a quick spot check proves this).
    – tlvince
    Dec 22, 2011 at 16:21
  • 1
    is it possible to add a column to the table and fill the column with a unique key then drop the column after the clean up?
    – Lumpy
    Dec 22, 2011 at 16:27
  • @Lumpy, if it helps to solve the problem then definitely (but to clarify, nothing currently exists that is unique).
    – tlvince
    Dec 22, 2011 at 16:32

3 Answers 3

3

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 !!!

1
  • It seems to have worked and very quickly at that. However, using a sum on the count field in my query above, I get 3847, whereas your suggestion finds 5500 duplicates. I'll check the results and get back to you. Thanks.
    – tlvince
    Dec 22, 2011 at 23:08
0

If you can add an ID column and fill it with a unique value for each record then you should be able to run a query like

DELETE FROM tbl_fields
WHERE <New ID Column> IN (SELECT MAX(<New ID Column>)
                            FROM tbl_fields
                           WHERE fieldnotes IS NULL 
                          GROUP BY fieldno,fieldserial,id,fielddate,fieldsid  
                         HAVING COUNT(*) > 1)

This would remove the duplicates then you could delete the

If you have enough duplication that you exceed SQLs limitation for the IN statment then You could insert the ID values for the IN statement into a temp table and run an exists against that.

2
  • I added a new column using alter table tbl_fields add unq mediumint not null auto_increment key and left your suggestion running for ~3 hours before killing it. Even with a subset of the table it appeared to hang. Is this to be expected?
    – tlvince
    Dec 22, 2011 at 23:05
  • 1
    This won't work as MySQL does not allow to use the tables that is being deleted in a sub-select.
    – user1822
    Jul 19, 2012 at 13:04
-1
CREATE TABLE friends_copy LIKE friends;

insert into friends_copy(name,dob)
select name,dob from friends group by name,dob having count(*)>=1
5
  • deleting duplicates and keeping only one copy Mar 16, 2019 at 3:40
  • insert into friends_copy(name,dob) select name,dob from friends group by name,dob having count(*)=1; Mar 16, 2019 at 3:41
  • if only unique records are to be kept Mar 16, 2019 at 3:42
  • Welcome to Database Administrators! Please explain how your query solves the author's problem; answers without explanation generally aren't received well.
    – Glorfindel
    Mar 16, 2019 at 8:07
  • A CREATE TABLE xyz AS SELECT my_fields FROM abc; is a. better way to go. See fiddle here.
    – Vérace
    Mar 17, 2019 at 11:41

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.