请教一个SQL命令的写法

Question

MySQL数据库，有三张表：

1. template表记录模板基本信息。
2. category表保存所有的模板分类信息。
3. template_category表记录模板和分类的关系，通过id关联，template和category是多对多的关系。

之前代码有bug导致创建了很多重复的模板分类（name一样但id不一样），并且每个模板分类下都可能分布了一些这个分类对应的模板（在template_category表有对应的记录）。

例如：
分类表数据

id  name    deleted_at
----------------------------
1   CRM     2021-07-05 14:51:22.006
2   CRM     2021-07-06 16:05:22.882
3   CRM     <null>
4   OA      2021-07-05 14:51:22.006
5   OA      <null>

template表数据

id  other_fields
----------------------------
1
2
3
...

tempalte_category表数据

template_id category_id
----------------------------
1           1
2           2
3           3
...

现在修复了bug，想用sql命令整理旧的错误数据。期望把名字相同的模板分类合并到同名未被删除的那一个，即整理为以下效果：

tempalte_category表数据

template_id category_id
----------------------------
1           3
2           3
3           3

不知有何方案？

Answer 1

动数据库之前要三思，像事务那样把所有操作考虑清楚了再一起动手。
在删除重复category的时候就应该同步修改关联表了，每delete一个category，就把所有引用它的地方修改成保留值。

用编程语言通过循环遍历把最后一步补上是最简单的，非要用SQL的话，不要在生产环境执行（不保证下面的语句没问题，自己在测试环境多试试），先备份表出来，执行完检查无误再同步回去：

update template_category
set template_category.category_id=temp.kept
from (select category.id,category2.id as kept
      from category
      left join (
          select id,name from category where deleted_at is null
          ) as category2
      on category.name=category2.name
      ) as temp
where template_category.category_id=temp.id

Answer 2

索引建好 重新跑一遍数据就好了吧😰

Answer 3

导出一份数据到新的数据库中，用程序遍历循环生成一张新表就行，大致上为：

1. 导出数据到新的数据库中，避免对原有业务造成影响;
2. 对需要检索的数据构建索引，其实不要也可以;
3. 先用程序循环生成基础的 template 表，将循环的数据删除；
4. 遍历其他表，并且对比 template 插入生成新表；
5. 将生成的表同步到生产环境即可；

Answer 4

感谢@liunux 提供的SQL命令思路。

生产环境备份，开发环境测试略过不表。最终解决方案如下：

select template_category.template_id, temp.kept as category_id from template_category left join
(
    select category.id, category.name, category2.id as kept
    from category
    left join (
      select id, name from category where deleted_at is null
      ) as category2
    on category.name=category2.name
) as temp
on template_category.template_category_id=temp.id
group by concat(template_category.template_id,temp.kept);

查询得到最终正确的template_id - category_id 的对应数据，然后删除原有的template_category表所有数据，按新的数据插入进去。

group by那句的原因是bug过程中存在将同一个template反复指定为name相同但id不同的category。

如果有直接update的方案，欢迎留下答案。SQL知识实在匮乏，感谢楼上诸位不吝赐教。