Merge 2 Users

[efil59]

Hi,
Is there a way to Merge 2 Users. ( i have the same member with 2 accounts).

[greyknight17]

It depends what you mean by merge here. Do you want to just merge the posts/topics for these two accounts? If so, you can update the database table so that all the messages from one of the accounts is renamed to reflect the other username.

Go into phpMyAdmin and click on your database. Then click on the SQL tab and copy paste the following code and run it:

Code Select Expand

UPDATE smf_messages SET ID_MEMBER='THEIR_NEW_ID' WHERE postername='OLD_USERNAME';

Replace the smf_ prefix if you are using a different one for your database. Replace THEIR_NEW_ID with the member ID you want to have the combined posts. Replace OLD_USERNAME with the username you want the posts to be copied from.

[Tony Reid]

Dont forget PM's

[efil59]

It depends what you mean by merge here. Do you want to just merge the posts/topics for these two accounts? If so, you can update the database table so that all the messages from one of the accounts is renamed to reflect the other username.

Go into phpMyAdmin and click on your database. Then click on the SQL tab and copy paste the following code and run it:

Code Select Expand

UPDATE smf_messages SET ID_MEMBER='THEIR_NEW_ID' WHERE postername='OLD_USERNAME';

Replace the smf_ prefix if you are using a different one for your database. Replace THEIR_NEW_ID with the member ID you want to have the combined posts. Replace OLD_USERNAME with the username you want the posts to be copied from.

Hi,
Yes that what i meant, but it doesn't work for me.

here is the SQL query:
UPDATE smf_messages SET ID_MEMBER='214' WHERE postername='ערן';

And i get this.
שגיאה

שאילתת SQL:

UPDATE smf_messages SET ID_MEMBER = '214' WHERE postername = 'ערן'

MySQL אמר: תיעוד
#1267 - Illegal mix of collations (latin1_swedish_ci,IMPLICIT) and (utf8_unicode_ci,COERCIBLE) for operation '='


It's because the USERNAME is in hebrew.

Is there another way?

[metallica48423]

sounds like your database table is in one collation but the columns are

I'm not sure how to fix it though... i've never had this particular problem

[efil59]

Thank you anyway!

I did managed, i found the name of the user in the  character that the database use, and did the SQL query.

[greyknight17]

There are some of the tables that needs to be updated as well if you want them. One of them is the PMs as indicated by Tony. If that's ok to leave out, then you should be set.

[warhonowicz]

what are the tables that need to be updated ... one of my users had issue logging in and created a new ID for himself and I would like to merge the two into one ... including everything, posts, pms etc

[Orange789]

I am not too good at SQL, but could you not just use the code:



UPDATE smf_messages SET ID_MEMBER='NEW ID' WHERE ID_MEMBER=OLD ID';

Then

UPDATE smf_personal_messages SET ID_MEMBER_FROM='NEW ID' WHERE ID_MEMBER-FROM='OLD ID';

then

UPDATE smf_personal_messages SET ID_MEMBER_FROM='NEW MEMBER NAME' WHERE ID_MEMBER-FROM='OLD MEMBER NAME';

then

UPDATE smf_pm_recipients SET ID_MEMBER_FROM='NEW ID' WHERE ID_MEMBER-FROM='OLD ID';


WARNING: Please don't action this until someone who actually knows can comment !

As I said, I dont really know SQL but just taking a logical approach to what I have read previously

[Informatics]

Assuming that first ID is 1 and the second is 2. And we are want to merge 2 to 1.

1. Updating the ID poster to all messages

UPDATE smf_messages SET ID_MEMBER=1 WHERE ID_MEMBER=2

2. Updating Personal Messages

UPDATE smf_personal_messages SET ID_MEMBER_FROM=1 WHERE ID_MEMBER_FROM=2

3. Updating Personal Messages Recipients

UPDATE smf_pm_recipients SET ID_MEMBER=1 WHERE ID_MEMBER=2

4. Polling

UPDATE smf_polls SET ID_MEMBER=1 WHERE ID_MEMBER=2

5. Themes

UPDATE smf_themes SET ID_MEMBER=1 WHERE ID_MEMBER=2

6. Topics

UPDATE smf_topics SET ID_MEMBER_STARTED=1 WHERE ID_MEMBER_STARTED=2
UPDATE smf_topics SET ID_MEMBER_UPDATED=1 WHERE ID_MEMBER_UPDATED=2

If you wondering about this, dont do it...

[Orange789]

Oh,

I werent too far off and my logical thinking was "kinda" right

[warhonowicz]

Thanks ... that got me a long way

[metallica48423]

sql is certainly strange to get a grip around

glad it worked for you guys

[warhonowicz]

I find SQL very logical, it's like another language and you have to learn the vocab ... slowly getting there though