How to Write a Query to Merge Two Accounts and their Activity Logs into One?

Question

We frequently have users that create multiple accounts and then end up storing the same lesson activity data more than once. Once they realize the error, then they contact us to merge the accounts into a single one that they can use.

I've been beating myself to death trying to figure out how to write a query in MySQL that will merge their activity logs into a single profile so that I can then delete the other profiles, but I still can't find the query that will work.

The tables look like this:

CREATE TABLE user_rtab (
 user_id int PRIMARY KEY,
 username varchar,
 last_name varchar,
 first_name varchar
);

CREATE TABLE lessonstatus_rtab (
 lesson_id int,
 user_id int,
 accessdate timestamp,
 score double,
);

What happens is that a user ends up taking the same lessons and also different lessons under two or more accounts and then they want to take all of their lesson statuses and have them assigned under one user account.

Can anyone provide a query that would accomplish this based on the lastname and firstname fields from the user table to determine all user accounts and then use only the user or username field to migrate all necessary statuses to the one single account?

Answer 1

How about this, assuming we are merging user_id 2 into 1.

This updates the lessons done under 2 that have not been done under 1.

UPDATE lessonstatus_rtab
SET user_id = 1
WHERE user_id = 2
AND NOT EXISTS
(SELECT *
 FROM lessonstatus_rtab e
 WHERE e.lesson_id = lessonstatus_rtab.lesson_id
 AND user_id = 1)

Anything leftover is a duplicate and can now be removed:

DELETE FROM lessonstatus_rtab
WHERE user_id = 2

Answer 2

Attempting to merge this data via last/first is a horrible idea, the more users you have, the more likely you are to mesh up incorrect entries. You have IDs on your tables for a reason, use them.

I don't see any reason why you can't say "I want to merge user 7 into 12" and then do the following:

UPDATE lessonstatus_rtab SET user_id=12 WHERE user_id=7;
DELETE FROM user_rtab WHERE user_id=7;

Answer 3

One of my current clients is facing a similar problem, except that they have dozens of tables that have to be merged. This is one reason to use a real life primary key (natural key). Your best bet is to try to avoid this problem before it even happens.

Another thing to keep in mind, is that two people can share both the same first and last name. Maybe you don't consider this an issue because of your user base, but if they're already creating multiple accounts how long is it until they start making up fake names or creating names that are almost the same, but not quite. Names are generally not a great thing to match on to determine if two people are the same or not.

As to the technical part of your question, it depends a lot on what the business rules are. If they have the same lesson in there twice with different scores do you use the highest score? How do you decide to which user account to link everything? No matter what, it's going to probably be a multi-step process.