Mysql: Merging related data when a column should become unique?

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.

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.

I've never seen two "from"s inside a single select-query before. I cannot run the select-query without update. Is this a special thing which only works together with "udpate", or an error?

– fishbone Jun 14 at 13:05.

I cant really gove you an answer,but what I can give you is a way to a solution, that is you have to find the anglde that you relate to or peaks your interest. A good paper is one that people get drawn into because it reaches them ln some way.As for me WW11 to me, I think of the holocaust and the effect it had on the survivors, their families and those who stood by and did nothing until it was too late.