给定一个mysql数据库，其表如下：
author:
+----+----------+
| id | name |
+----+----------+
| 1 | John |
| 2 | Peter |
| 3 | Peter |
+----+----------+
article:
+----+-----------+------+
| id | author_id | text |
+----+-----------+------+
| 1 | 2 | ... |
| 2 | 3 | ... |
| 3 | 3 | ... |
+----+-----------+------+
author表的name列不是偶然设置为unique的。现在我必须将相关文章“合并”到一个相关作者中，即将文章2和3的作者id设置为2。之后我想使name列唯一。
我无法手动重新分配文章，因为受影响的记录太多。但我认为这个问题可能有现成的解决方案/片段。
最佳答案
要更新article表，这将完成以下操作：
update article art
set art.author_id = (select min(aut.id)
from author aut
where aut.name = (select a.name
from author a
where a.id = art.author_id));
select * from article;
+ ------- + -------------- + --------- +
| id | author_id | text |
+ ------- + -------------- + --------- +
| 1 | 2 | |
| 2 | 2 | |
| 3 | 2 | |
+ ------- + -------------- + --------- +
3 rows
如果您喜欢更紧凑的更新（和更优化的更新），则可以使用此更新，其工作方式相同：
update article art
set art.author_id = (select min(aut.id)
from author aut
inner join author a on a.name = aut.name
where a.id = art.author_id);
最后，要删除额外的作者，您需要
delete a
from author a
inner join (
select name, min(id) as min -- this subquery returns all repeated names and their smallest id
from author
group by name
having count(*) > 1) repeated on repeated.name = a.name
where a.id > repeated.min; -- delete all repeateds except the first one
select * from author;
+ ------- + --------- +
| id | name |
+ ------- + --------- +
| 1 | John |
| 2 | Peter |
+ ------- + --------- +
2 rows
这适用于任意数量的重复作者集。
希望这有帮助
关于mysql - 当列应该唯一时合并相关数据，我们在Stack Overflow上找到一个类似的问题：https://stackoverflow.com/questions/6343600/