CODEWITHSUNDEEP
mysqlsqlduplicates
Shifty
Shifty

Reputation: 112

How can I delete duplicated rows based on more column in SQL?

I have got a database with movies and it's links. One movie may has more links. Unfortunately some of them has the same link twice.

For example:

row1 Alien vs. Predator http://www.avplink1
row2 Alien vs. Predator http://www.avplink1
row3 Alien vs. Predator http://www.avplink2
row4 Alien vs. Predator http://www.avplink3
row5 Minions http://www.minionslink1
row6 Minions http://www.minionslink1

I would like to delete that rows which are more than one in the table, but keep one of them. So I would like this:

row1 Alien vs. Predator http://www.avplink1
row3 Alien vs. Predator http://www.avplink2
row4 Alien vs. Predator http://www.avplink3
row5 Minions http://www.minionslink1

How can I write an SQL query which delete these rows? Thanks!

EDIT:

I solved with this code:

DELETE a            
FROM links a            
JOIN (SELECT MIN(id) id, movielink
FROM links 
GROUP BY movielink) b ON a.movielink= b.movielink 
AND a.id <> b.id

Thanks everyone the help!

Upvotes: 0

Views: 35

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1269493

This is a pain without a unique id in each row (all tables should have a primary key). Probably the easiest way is to use a temporary table in that case:

create temporary table tempt as
    select distinct movie, link
    from t;

truncate table t;

insert into t(movie, link)
    select movie, link
    from tempt;

There are simpler ways if you have a unique id. After doing this, put a unique index on the table to prevent this from happening in the future:

create unique index idx_t_movie_link on t(movie, link);

Actually, I think this statement will also delete duplicate rows, but I don't recommend using index creation to delete rows.

EDIT:

If you have a unique row identifier, then you can just do:

delete t
    from t join
         (select movie, link, min(rowid) as minrowid
          from t
          group by movie, link
         ) tt
         on t.movie = tt.movie and t.link = tt.link and t.rowid <> tt.minrowid

Upvotes: 1

Darren H
Darren H

Reputation: 902

This is almost a duplicate of this question except put

delete from

instead of

select * from

Upvotes: 1

Related Questions