trevor1940
asked on
SQL: query for removing duplicates
Hi
I'm attempting to clean up a database and need to remove duplicate entries
manually I'm doing this
Is there a way I can do this via script?
this found 58 entries (OK not a big number but I'm lazy)
I'm attempting to clean up a database and need to remove duplicate entries
manually I'm doing this
select * from person where person.tmdb_id = 17867;
-- Gives
id name tmdb_id profile_path
412 Thomas McCarthy 17867 /bz5ppWS58ryvKG4QQpIsqRXY68w.jpg
28522 Tom McCarthy 17867 /bz5ppWS58ryvKG4QQpIsqRXY68w.jpg
-- Pick 1 usually the lowest id
update movielinkperson set personId = 412 where personId = 28522;
delete from person where id = 28522;
Is there a way I can do this via script?
this found 58 entries (OK not a big number but I'm lazy)
select COUNT(*) from person GROUP BY person.tmdb_id HAVING COUNT(*)>1;
ASKER
Hi
That seems to remove the duplicates from peron_tmp but How dose this update the movielinkperson table
as in the opening question?
That seems to remove the duplicates from peron_tmp but How dose this update the movielinkperson table
as in the opening question?
update movielinkperson set personId = 412 where personId = 28522;
OR
update movielinkperson set personId = <KEPT personId > where personId = <REMOVED personId >;
Do you have all the person IDs in movielinkperson table?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Hi Run your query in sqlfiddle which looks like it's working
FYI The movielinkperson Table has
id, movie_id , personid , and role it basically links a person to a movie
I wrote a short C# script to do this
FYI The movielinkperson Table has
id, movie_id , personid , and role it basically links a person to a movie
I wrote a short C# script to do this
Note the script below keeps the "earliest" entry from the table or "the lowest id" as you mentioned however if you want to keep the "latest" then just replace the MIN with MAX and the ASC with DESC in the ORDER BY below.