deleting a duplicate row?

Question

Possible Duplicate:
How to delete duplicate rows with SQL?

I have this query which returns duplicate rows from a table in terms of the same name and year.

How can I delete the duplicate records, so there will only be unique values in the table?

The query is:

SELECT movies.movie_name, movies.year 
FROM movies
INNER JOIN 
(
    SELECT movie_name, year 
    FROM movies
    GROUP BY movie_name,year 
    HAVING count(movie_id) > 1
) dup 
    ON movies.movie_name = dup.movie_name
    and movies.year = dup.year

Do you have a unique id for each row in your movies tables? – Damp Nov 28 '12 at 18:41 — Damp, Nov 28 '12 at 18:41

score 2 · Answer 1 · answered Nov 28 '12 at 18:42

2

DELETE statments can make use of subqueries to filter the items to be deleted.

Read through this

http://www.w3resource.com/sql/delete-statement/delete-with-subqueries.php

answered Nov 28 '12 at 18:42

AlanH

89
3

fthiella · Answer 2 · 2012-11-29T00:20:20.787

This query shows all the duplicates:

SELECT movie_name, year 
FROM movies
GROUP BY movie_name,year 
HAVING count(movie_id) > 1

And this shows just a movie_id for every movie (duplicated or not), these are the rows to keep:

SELECT movie_name, year, min(movie_id) min_movie_id
FROM movies
GROUP BY movie_name,year

To delete duplicates you could use this:

DELETE movies
FROM
  movies inner join
  (SELECT movie_name, year, min(movie_id) min_movie_id
   FROM movies
   GROUP BY movie_name,year) keep
  on movies.movie_name = keep.movie_name
     and movies.year=keep.year
     and movies.movie_id<>keep.min_movie_id

or you could also use this query:

delete from movies
where
  (movie_name, year, movie_id) not in
  (select movie_name, year, min_movie_id from
    (SELECT movie_name, year, min(movie_id) min_movie_id
     FROM movies
     GROUP BY movie_name,year) keep)

deleting a duplicate row?

2 Answers2