Merging related data when a column should become unique

Given a mysql-database with tables as follows:

author:
+----+----------+
| id | name     |
+----+----------+
| 1  | John     |
| 2  | Peter    |
| 3  | Peter    |
+----+----------+

article:
+----+-----------+------+
| id | author_id | text |
+----+-----------+------+
| 1  | 2         | ...  |
| 2  | 3         | ...  |
| 3  | 3      开发者_开发技巧   | ...  |
+----+-----------+------+

The author-table's name-column wasn't set to unique by accident. Now I have to "merge" related articles into one of the related authors, i.e. set author_id of articles 2 and 3 to 2. I want to make the name-column unique afterwards.

I cannot reassign the articles manually, because there are too many affected records. But I thought there may be a ready solution / snippet for this problem.

---

To update your article table, this will do the trick:

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

if you prefer a more compact update (and more optimized), then you can use this one, that works the same way:

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);

Finally, to delete the extra authors, you need

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

This works for any number of repeated sets of authors.

Hope this helps

---

You can do an update article first to use the lowest author id having the same name

UPDATE art SET art.author_id =
    (SELECT MIN(a1.id) FROM author a1 WHERE a1.Name = a2.name
        FROM article art INNER JOIN author a2 ON art.author_id = a2.id)

Then delete the higher author having the same name

PS. I have not tested the SQL but should work.