sql - Removing rows when column has same value, and relating those rows to the same primary key in MySQL -

i'm working on project mysql 5.7 deals movies database. have following tables (that not allowed change):

create table `movies` (   `id` int not null auto_increment,   `title` varchar(100) not null default '',   `director` varchar(100) not null default '',   primary key(`id`) ) engine=innodb default charset=latin1;  create table `genres` (   `id` int not null auto_increment,   `name` varchar(32) not null default '',   primary key (`id`) ) engine=innodb default charset=latin1;  create table `genres_in_movies` (   `genre_id` int not null,   `movie_id` int not null,   foreign key (`genre_id`) references `genres`(`id`),   foreign key (`movie_id`) references `movies`(`id`) ) engine=innodb default charset=latin1; 

i had records had updated reflect valid genres. unfortunately have bunch of duplicate genres.name though genres.id differs. consolidate equivalent genres.name single genres.id , genres.name. example, if have table:

genres (old) ================== id  |  name      | ================== 2   | romance    | ------------------ 6   | romance    | ------------------ 45  | romance    | ================== 

i want end single entry genres.id = 2 , genres.name = 'romance'.

i following query:

delete genres genres.id not in (select *                         (select min(g.id)                              genres g                              group g.name)                         x); 

but want able update genres_in_movies.genre_id reflect genres.id change movies table doesn't break. how can update genres_in_movies table well?

first update genres_in_movies:

update genres_in_movies gin join        genres g        on gin.genre_id = g.id join        (select g2.name, min(g2.id) minid         genres g2         group g2.name        ) g2        on g2.name = g.name , g2.minid <> g.id     set gin.genre_id = g2.minid; 

then delete.

btw, can rephrase delete using left join:

delete g     genres g left join          (select min(g.id) minid           genres g           group g.name          ) gmin          on g.id = gmin.minid     gmin.minid null;