Mistakenly added duplicate records. How do I delete them?

Question

EDIT

This query is not working, any idea why?

select `key`, distinct `filename`, `url`, `processed`, `timestamp` from snaps;

It says to check syntax near 'distinctfilename``

I have the following table

CREATE TABLE IF NOT EXISTS `snaps` (
  `filename` varchar(255) COLLATE utf8_bin NOT NULL,
  `url` text COLLATE utf8_bin NOT NULL,
  `processed` int(11) NOT NULL,
  `timestamp` int(11) NOT NULL,
  KEY `key` (`key`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_bin;

I exported the rows of the table and mistakenly imported it back into the same table. So now I have two rows of everything. filename is unique.

How can I delete the duplicate records?

this may help http://stackoverflow.com/questions/3777633/delete-duplicate-rows-dont-delete-all-duplicate — Nishant, Feb 22 '11 at 06:44

score 1 · Accepted Answer · answered Feb 22 '11 at 06:42

1

SELECT DISTINCT into a temporary table, flush the first one, and move them back again.

answered Feb 22 '11 at 06:42

That's embarrassingly simple. I tried the query in the edit but it's not working. What's wrong with it? – HyderA Feb 22 '11 at 06:49
1

Your distinct goes at the beginning, after SELECT. – Feb 22 '11 at 06:50

score 0 · Answer 2 · answered Feb 22 '11 at 06:56

0

If you have all the original unduplicated rows of the table, can you not just

TRUNCATE <table> and then reimport them?

answered Feb 22 '11 at 06:56

Simon

9,197
13
72
115

score 0 · Answer 3 · answered Feb 22 '11 at 07:00

0

Does second set of rows has an identical timestamp from the insert? If so, you could DELETE based on that timestamp.

Alternately, you could SELECT INTO OUTFILE, delete the last half, TRUNCATE TABLE and then LOAD DATA INFILE.

answered Feb 22 '11 at 07:00

Paul Schreiber

12,531
4
41
63

Mistakenly added duplicate records. How do I delete them?

3 Answers3