How do I write a query to combine two accounts and their activity logs into one?
We often have users who create multiple accounts and then save the same lesson activity data more than once. As soon as they realize the error, they will contact us to merge the accounts into 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 one profile so that I can then delete other profiles, but I still can't find a 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 the user gets the same lessons as well as different lessons on two or more accounts, and then they want to take all their lesson statuses and assign them under one user account.
Can anyone provide a query that will execute this based on the lastname and firstname fields from the users table to identify all user accounts, and then use only the user field or username to transfer all the required statuses into one account?
source to share
One of my current clients is facing a similar problem, except they have dozens of tables that need to be joined. This is one of the reasons for using real life primary key (natural key). Your best bet is to try and avoid this problem before it happens.
Another thing to keep in mind is that two people can share the same last name and last name. You may not think this is a problem because of your user base, but if they are already creating multiple accounts, how long does it take until they start making up fake names or create names that are almost the same but not quite. Names are generally not very suitable for determining whether two people are the same or not.
As for the technical part of your question, it depends on what the business rules are. If they have the same lesson twice with different scores, are you using the highest score? How do you decide which user account should link everything? Regardless, it will likely be a multi-step process.
source to share
Trying to combine this data using last / first is a terrible idea, the more users you have, the more likely you are to combine the wrong records. You have ids on your tables for a reason, use them.
I see no reason why you cannot say "I want to merge user 7 to 12" and then follow these steps:
UPDATE lessonstatus_rtab SET user_id=12 WHERE user_id=7;
DELETE FROM user_rtab WHERE user_id=7;
source to share
How about this, assuming we are concatenating user_id 2 into 1.
This updates the lessons completed under 2 that were not completed in 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)
Everything else is a duplicate and can now be removed:
DELETE FROM lessonstatus_rtab
WHERE user_id = 2
source to share