Merge MYSQL rows, totalling some columns, updating ID's in another table

Question

I have a MySQL database with several tables, two of which look like this:

players

+-----+---------+-------------------+----------------+----------------+--------------+---------------------+ | id | base_id | name | total_searches | auctions_found | auctions_won | created_at | +-----+---------+-------------------+----------------+----------------+--------------+---------------------+ | 283 | 177683 | Christian Eriksen | 12 | 2 | 1 | 2019-05-29 11:25:08 | | 160 | 177683 | Christian Eriksen | 24 | 4 | 1 | 2019-05-29 11:25:08 | | 76 | 345345 | Yan Sommer | 12 | 1 | 3 | 2019-05-29 11:25:08 | | 712 | 4353 | Yannick Carrasco | 42 | 5 | 12 | 2019-05-29 11:25:08 | +-----+---------+-------------------+----------------+----------------+--------------+---------------------+

transactions

+----+-----------+---------------------+ | id | player_id | updated_at | +----+-----------+---------------------+ | 1 | 283 | 2019-05-29 11:25:08 | | 2 | 160 | 2019-05-29 11:25:08 | | 3 | 76 | 2019-05-29 11:25:08 | +----+-----------+---------------------+

As you can see, there are duplicates in the players table (Christian Eriksen). I would like to merge these rows, totalling the total_searches, auctions_found and auctions_won columns. There are several other columns in this table (some omitted here for brevity) which are either the same (eg. base_id) or it's not hugely important which value is retained (eg. created_at).

What is important is the id. Either a new id should be created or, ideally, one of the existing ids retained. The transactions table would then need to be updated with the new player_id.

+-----+---------+-------------------+----------------+----------------+--------------+---------------------+ | id | base_id | name | total_searches | auctions_found | auctions_won | created_at | +-----+---------+-------------------+----------------+----------------+--------------+---------------------+ | 160 | 177683 | Christian Eriksen | 36 | 6 | 2 | 2019-05-29 11:25:08 | | 76 | 345345 | Yan Sommer | 12 | 1 | 3 | 2019-05-29 11:25:08 | | 712 | 4353 | Yannick Carrasco | 42 | 5 | 12 | 2019-05-29 11:25:08 | +-----+---------+-------------------+----------------+----------------+--------------+---------------------+ +----+-----------+---------------------+ | id | player_id | updated_at | +----+-----------+---------------------+ | 1 | 160 | 2019-05-29 11:25:08 | | 2 | 160 | 2019-05-29 11:25:08 | | 3 | 76 | 2019-05-29 11:25:08 | +----+-----------+---------------------+

I don't have any knowledge of SQL so I haven't tried anything yet. Some pointers or ideally a complete solution would be appreciated.

Gordon Linoff · Accepted Answer · 2019-05-29 14:55:45Z

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

Collectives™ on Stack Overflow

Merge MYSQL rows, totalling some columns, updating ID's in another table

1 Answer 1

Comments

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Related