This is quite complicated. I would suggest:

• Backup the tables!
• Update the id in the transactions table to be the maximum id .
• Fix the players table.
• Fix your data model.

That is:

update transactions t join players p on t.player_id = p.id join (select p2.name, max(p2.id) as max_id from players p2 group by p2.name having count(*) > 1 ) p2 on p2.name = p.name -- or should this be "base_id" set t.player_id = p2.max_id where t.player_id <> p2.max_id; 

Then, to update the table, I recommend emptying it and recreating it:

create table players_temp as select max(id) as id, base_id, name, sum(total_searches) as total_searches, sum(auctions_found) as auctions_found, sum(auctions_won) as auctions_won, min(created_at) as created_at from players group by base_id, name; -- or whatever truncate table players; insert into players (id, base_id, name, total_searches, auctions_found, auctions_won, created_at) select id, base_id, name, total_searches, auctions_found, auctions_won, created_at from temp_players; 

Then fix the data model:

alter table players add constraint unq_players_name unique (name); alter table players add constraint unq_players_base_id unique (base_id); 

This is quite complicated. I would suggest:

• Backup the tables!
• Update the id in the transactions table to be the maximum id .
• Fix the players table.
• Fix your data model.

That is:

update transactions t join players p on t.player_id = p.id join (select p2.name, max(p2.id) as max_id from players p2 group by p2.name having count(*) > 1 ) p2 on p2.name = p.name -- or should this be "base_id" set t.player_id = p2.max_id where t.player_id <> p2.max_id; 

Then, to update the table, I recommend emptying it and recreating it:

create table players_temp as select max(id) as id, base_id, name, sum(total_searches) as total_searches, sum(auctions_found) as auctions_found, sum(auctions_won) as auctions_won, min(created_at) as created_at from players group by base_id, name; -- or whatever truncate table players; insert into players (id, base_id, name, total_searches, auctions_found, auctions_won, created_at) select id, base_id, name, total_searches, auctions_found, auctions_won, created_at from players_temp; 

Then fix the data model:

alter table players add constraint unq_players_name unique (name); alter table players add constraint unq_players_base_id unique (base_id);