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

Question

我正在使用 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;

我的记录必须更新以反射(reflect)一些有效的流派。不幸的是，尽管 genres.id 不同，但现在我有一堆重复的 genres.name 。我想做的是将所有等效的 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 以反射(reflect) genres.id 更改，以便 movies 表不会'不要打破。那么如何更新 genres_in_movies 表呢？

Answer 1

首次更新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;

然后进行删除。

顺便说一句，您可以使用 LEFT JOIN 重新表述 DELETE:

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;