Is it possible to remove duplicate entries? (1 Viewer)

[Zasurus]

I don't know if anyone else wants this but I get the problem with duplicate entrys every time I move some files around or when I change a drive letter. I get around this by running the following SQL

DELETE FROM Local_episodes
WHERE CompositeID IN (
SELECT DISTINCT LE1.CompositeID
FROM local_episodes as LE1
, local_episodes as LE2
WHERE LE1.CompositeID = LE2.CompositeID
AND LE1.EpisodeFilename <> LE2.EpisodeFilename
ORDER BY LE1.CompositeID
)

It deletes from the Local_Episodes table which only contains information on the local files so all watches info is kept intact. And it only deletes files that are duplicated. So if there are 2 or 3 duplicates of an episode it will delete them all.

Then when you rescan (ether though the config program or just in the background in mp) it will only add the current and correct file info.

As it only deletes the files that are duplicated it shouldn't take long to get the media info again unless you moved all your files in one go or change a drive letter like I did 3000 files... Mind you that didn't take that long maybe 10-20mins in the config program.

Hope this helps.

If you don't know what SQL is or how to use it I surgest you don't try it as you could delete all your media portal data including your watched flags etc...

Regards,

Zas

 

[rayone]

I do the same with duplicate Movieinfo entries:

update actorlinkmovie set idMovie = (select idMovie from movieinfo where strTitle = (select strTitle from movieinfo where idMovie = actorlinkmovie.idMovie))
                                   
       where idMovie in(select m.idMovie from movieinfo m inner join 
                                  (select * from movieinfo as d where idMovie > (select min(idMovie) from movieinfo as a
                                    where    a.strTitle = d.strTitle    
                                    and a.IMDBID = d.IMDBID
                                   )) as mi on m.idMovie = mi.idMovie);

update files set idMovie = (select idMovie from movieinfo where strTitle = (select strTitle from movieinfo where idMovie = files.idMovie))
       where idMovie in (select m.idMovie from movieinfo m inner join 
                                  (select * from movieinfo as d where idMovie > (select min(idMovie) from movieinfo as a
                                    where    a.strTitle = d.strTitle    
                                    and a.IMDBID = d.IMDBID
                                   )) as mi on m.idMovie = mi.idMovie);
                                   
update genrelinkmovie set idMovie = (select idMovie from movieinfo where strTitle = (select strTitle from movieinfo where idMovie = genrelinkmovie.idMovie))
       where idMovie in(select m.idMovie from movieinfo m inner join 
                                  (select * from movieinfo as d where idMovie > (select min(idMovie) from movieinfo as a
                                    where    a.strTitle = d.strTitle    
                                    and a.IMDBID = d.IMDBID
                                   )) as mi on m.idMovie = mi.idMovie);
                                   
delete from movie where idMovie in(select idMovie from movieinfo as d where idMovie >
    (
       select min(idMovie) from movieinfo as a
        where    a.strTitle = d.strTitle    
        and a.IMDBID = d.IMDBID
        )
);

delete from movieinfo where idMovie in(select idMovie from movieinfo as d where idMovie >
    (
       select min(idMovie) from movieinfo as a
        where    a.strTitle = d.strTitle    
        and a.IMDBID = d.IMDBID
        )
);

After a new scan I have +900 idMovie, with 112 duplicates. The above fixes the issue.