Concatenate duplicate rows with additional info (MySQL)

Question

So I'm looking for an MySQL command to deduplicate rows in a table like this:

ID   origin   firstname   lastname   job         alternative_origin
1    table0   Andrew      Miller     Plumber
2    table1   Andrew      Miller     plumb.
3    table0   Joe         Armstrong  Taylor
4    table1   Phil        Marshall   Carpenter

I would like to concatenate all those rows where firstname AND lastname are identical so the table looks like this:

ID   origin   firstname   lastname   job         alternative_origin
1    table0   Andrew      Miller     Plumber     table1
3    table0   Joe         Armstrong  Taylor
4    table1   Phil        Marshall   Carpenter

Any help would be greatly appreciated.

Answer 1

For this sample data where there can not exist more than 2 duplicates for each name, use this:

update tablename t 
inner join tablename tt 
on tt.firstname = t.firstname and tt.lastname = t.lastname and tt.id > t.id
set t.alternative_origin = tt.origin;

to update the table and then delete the duplicate rows:

delete t
from tablename t inner join tablename tt 
on tt.firstname = t.firstname and tt.lastname = t.lastname and tt.id < t.id;

See the demo.
Results:

> ID | origin | firstname | lastname  | job       | alternative_origin
> -: | :----- | :-------- | :-------- | :-------- | :-----------------
>  1 | table0 | Andrew    | Miller    | Plumber   | table1            
>  3 | table0 | Joe       | Armstrong | Taylor    | null              
>  4 | table1 | Phil      | Marshall  | Carpenter | null