Automatically merge Users with same eMail

[Phalynx]

The board I was migrating from is UBB6.x, which has not checked for duplicate eMails. Now I have the problem that from the around 5500 Users there are a lot that has duplicate eMail Adresses. It's very time consuming to go through the tables and do a merge for every user. Is there a way to do this automatically? My Testboard has no existing users before import.

[Colin F]

No, there's no function to do that.

[Phalynx]

Ok... what can I use to ask MySQL to output only usernames that have conflicting eMails?

[Steve Machol]

What do you mean exactly? How would MySQL know this?

[Phalynx]

What do you mean exactly? How would MySQL know this?

I mean, how to find out the users that have multiple accounts with the same email?

And another issue with the emails:
I've two users with the same name and eMail address (like importedUser), if I merge importedUser with User, which of them is beeing merged?

[Steve Machol]

Sorry, I don't know the query to do that. Not even sure if it's possible frankly.

[Phalynx]

Thanks anyway. I've to write a script to do that.

[Colin F]

You can use this query to select those users:

SELECT u1.username AS user
FROM user AS u1
LEFT JOIN user AS u2 ON (u1.email = u2.email)
WHERE u2.email != '' AND u1.userid != u2.userid;