How can I delete all rows with duplicates in one column using MySQL?

Question

I have a table which has duplicate values in one column, title, so the title column has multiple rows with the same values.

I want to delete all duplicates except one where the title is the same.

What sort of query can I perform to accomplish this?

  Title     Subject             Description               Created_at
Something  Somethingsubject    Somethingdescription       2016-04-13 16:37:10  
Something  Anothersubject      Anotherdescription         2016-04-11 16:37:10
Something  Thirdsubject        Thirdsubject               2016-04-14 16:37:10
NumberTwo  NumberTwoSubject    NumberTwoSubject           2016-04-12 16:37:10
NumberTwo  AnotherNumberTwo    AnotherNumberTwoDescripti  2016-04-15 16:37:10

I would like to delete all duplicates, leaving just one, preferably the oldest record, so that the only remaining records would be:

Title        Subject            Description            Created_at
Something  Anothersubject     Anotherdescription    2016-04-11 16:37:10
NumberTwo  NumberTwoSubject    NumberTwoSubject     2016-04-12 16:37:10

Giorgos Betsos · Accepted Answer · 2016-04-28 18:45:09Z

8

You can do a self-join DELETE:

DELETE t1
FROM mytable t1
JOIN (SELECT Title, MAX(Created_at) AS max_date
      FROM mytable
      GROUP BY Title) t2
ON t1.Title = t2.Title AND t1.Created_at < t2.max_date

Demo here

answered Apr 28, 2016 at 18:45

Giorgos Betsos

72.3k10 gold badges69 silver badges103 bronze badges

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

Comments

Tudor Constantin · Accepted Answer · 2016-04-28 18:44:02Z

1

Do a backup first, for obvious reasons, but this should work:

delete from your_table where id not in (select id from your_table group by title)

Where id is the column that stores the primary key for your_table

answered Apr 28, 2016 at 18:44

Tudor Constantin

26.9k7 gold badges54 silver badges73 bronze badges

4 Comments

Ethan F. Over a year ago

At least you didn't solve the " leaving just one, preferably the oldest record" requirement. Plus what if no "id" there.

Tudor Constantin Over a year ago

No doubt you can always present a much better solution

Ethan F. Over a year ago

Sorry man didn't mean any offense. Just tried to understand the answer.

Tudor Constantin Over a year ago

Sorry I took it as one :)

Ethan F. · Accepted Answer · 2016-04-28 19:37:28Z

0

My idea is to export results into new table. Then replace the old one with the new one. Pros are 1. You can check if the results are what you want. 2. You don't loose original data

create table new_mytable select * from (select * from mytable order by created_at) as b group by b.title

answered Apr 28, 2016 at 19:37

Ethan F.

2511 silver badge7 bronze badges

Comments

FrenchTechLead · Accepted Answer · 2016-04-28 18:47:30Z

-1

DELETE * FROM table WHERE Title ="Something" and Subject <> "Somethingsubject"

in some versions of sql not equal <> is !=

answered Apr 28, 2016 at 18:47

FrenchTechLead

1,1563 gold badges13 silver badges23 bronze badges

2 Comments

Ethan F. Over a year ago

This answer is ... very interesting.

Tin Tran Over a year ago

@EthanF. I agree, this answer will definitely provide some job security because of possible future work load until your supervisor reads it

Collectives™ on Stack Overflow

How can I delete all rows with duplicates in one column using MySQL?

4 Answers 4

Comments

4 Comments

Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

Comments

4 Comments

Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related