I have a database for our webmail (roundcube) that has become fairly garbled over the past year. Originally usernames were formated like so 'john.smith+domain.com' but was later changed to 'email@example.com". Even more recently the "+" has been replaced by "@" in all instances within the database. However this has caused some issues, there are now duplicate fields for all addresses. Recently peoples address books have not been showing up because they were linked to the older username format which had a different userID value in the Contacts table. I know how to do this manually or even semi-manually (running update and replace query) but even then I would have to do it for each address. Any help would be greatly appreciated.
Example in an attempt to clarify.
In the 'users' table.
| User_ID | | username |
In the 'contacts' table the user_id is used to link the addresses (contact_id's) to the proper username. So where im at now we have 'user_id' 103 with about a hundred contacts and only five for 'user_id' 32 but when logging in to the webmail through the firstname.lastname@example.org address only the contacts from 'user_id' 32 are visable in the address book.
Is there a easy way to either A) link all the user_id's that have the same 'username' field value
B) replace all one user_id in the Contacts table with the other, C) anything that would possibly make fixing this easier!