Latest Post

Error: No input file specified.

While trying to run a codeigniter app the other day I came across this error just after I had finished uploading the files and making the ne...

Monday, February 28, 2011

Deleting Duplicate Rows in a MySQL Database

Assuming the following bad table design:
CREATE TABLE bad_table2 (
id INT NOT NULL UNIQUE AUTO_INCREMENT,
name VARCHAR(20) NOT NULL
);

INSERT INTO bad_table2(id,name) VALUES
(1,'Things Fall Apart'),
(2,'Things Fall Apart'),
(3,'The Famished Road'),
(4,'Things Fall Apart'),
(5,'The Famished Road'),
(6,'Thirteen cents'),
(7,'Thirteen cents');

I can remove duplicates easily with the following line:
alter ignore table bad_table2 add unique index `unique_index` (name);

And then remove the added index.
alter table bad_table2 drop index `unique_index`;

Source: Comment by Paul Swarthout @ databasejournal.com

No comments:

Post a Comment

Chitika Ads