CODEWITHSUNDEEP
mysqlsql
KinsDotNet
KinsDotNet

Reputation: 1560

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

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

Upvotes: 0

Views: 73

Answers (4)

Ethan F.
Ethan F.

Reputation: 251

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

Upvotes: 0

FrenchTechLead
FrenchTechLead

Reputation: 1146

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

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

Upvotes: -1

Giorgos Betsos
Giorgos Betsos

Reputation: 72165

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

Upvotes: 8

Tudor Constantin
Tudor Constantin

Reputation: 26861

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

Upvotes: 1

Related Questions