Remove duplicate rows from mysql table

Question

I have a mysql table called urls

It has the following fields

url
title (default null)
content (default null)

My table has many duplicate rows.

I would like to remove those duplicates.

For example

google.com - Google - search engine
google.com - null - null
google.com - Google - search engine

Now I would like to drop the rows where title = null plus the duplicate row.

I just want the url to be unique with no duplicate rows.

Can some tell me how?

Does this table have a primary key field ID ?

valex
– valex

2013-08-19 06:07:18 +00:00
Commented Aug 19, 2013 at 6:07 — valex
– valex, Commented Aug 19, 2013 at 6:07
No.. url is the primary key

PrivateUser
– PrivateUser

2013-08-19 06:09:34 +00:00
Commented Aug 19, 2013 at 6:09 — PrivateUser
– PrivateUser, Commented Aug 19, 2013 at 6:09
@Giri, for begin, mark your url field as unique

maximkou
– maximkou

2013-08-19 06:15:35 +00:00
Commented Aug 19, 2013 at 6:15 — maximkou
– maximkou, Commented Aug 19, 2013 at 6:15

Ilion · Accepted Answer · 2013-08-19 06:18:01Z

3

This is the simplest:

create tableB like tableA;
INSERT INTO tableB SELECT DISTINCT * FROM tableA WHERE title IS NOT NULL;
DROP tableA;
RENAME tableB TO tableA;

Even in a production environment the drop and rename is usually quick enough to be near invisible to any users. Double check to make sure you end up with the appropriate indexes.

answered Aug 19, 2013 at 6:18

Ilion

6,8923 gold badges28 silver badges47 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

RadhaKrishna · Accepted Answer · 2013-08-19 06:15:53Z

0

you can add url as the unique index.the following query will add the unique index and remove the deuplicates.

alter ignore table urls add unique index(url);

If you dont want to add unique index, then the alternate is to create a temp table with unique index,copy the data,remove the duplicate and transfer back to your original table.

answered Aug 19, 2013 at 6:15

RadhaKrishna

3123 silver badges13 bronze badges

Comments

kevinm · Accepted Answer · 2013-08-19 06:26:18Z

0

    DELETE FROM urls WHERE title IS NULL 
           OR url IN (SELECT a.URL from urls a, urls b WHERE a.url=b.url);

answered Aug 19, 2013 at 6:26

kevinm

4752 silver badges8 bronze badges

Collectives™ on Stack Overflow

Remove duplicate rows from mysql table

3 Answers 3

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related