I am running the following SQL (extract shown below)
INSERT INTO users (user_id, username, username_clean) SELECT member_id, username, lower(REPLACE(username, '.', '_')) FROM members
The problem arises when lower(REPLACE(username, '.', '_')) produces a duplicate that already exists in username_clean as the users table does not permit duplicates.
Is there a SQL or PHP solution to append a suffix such as 1,2,3 etc after such a duplicate so that they do not exist in the users table?
I could do this manually but as I want to run this routine many times this is not a solution.
Many thanks in advance