mysql - 当列具有相同的值时删除行，并将这些行与MySQL中的相同主键关联

我正在使用MySQL 5.7处理电影数据库的项目。我有以下表格（不允许更改）：

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;

我的记录必须进行更新以反映一些有效的genres。不幸的是，即使genres.name不同，现在我还是有一堆重复的genres.id。我想做的是将所有等效的genres.name合并为一个genres.id和genres.name。例如，如果我有此表：

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

我只想以genres.id = 2和genres.name = 'Romance'的单个条目结束。

我可以使用以下查询轻松完成此操作：

DELETE FROM genres
WHERE genres.id NOT IN (SELECT *
                       FROM (SELECT MIN(g.id)
                             FROM genres g
                             GROUP BY g.name)
                       x);

但我也希望能够更新genres_in_movies.genre_id来反映genres.id的更改，以使movies表不会中断。那么，如何更新genres_in_movies表呢？

最佳答案

第一次更新genres_in_movies：

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

然后进行删除。

顺便说一句，您可以使用DELETE重新定义LEFT JOIN：

DELETE g
    FROM genres g LEFT JOIN
         (SELECT MIN(g.id) as minid
          FROM genres g
          GROUP BY g.name
         ) gmin
         ON g.id = gmin.minid
    WHERE gmin.minid IS NULL;

关于mysql - 当列具有相同的值时删除行，并将这些行与MySQL中的相同主键关联，我们在Stack Overflow上找到一个类似的问题：https://stackoverflow.com/questions/37426528/