0

Hi

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

Mark

2
Contributors
1
Reply
2
Views
5 Years
Discussion Span
Last Post by pritaeas
0

Shouldn't you do such a check before inserting? I know the problem could still arise then, but it would be much cleaner to let the user select a different name. I know most would prefer that over having a number attached without knowing.

If you are stuck with this logic, then you could select all matching username_clean, but you'd have to create a check loop somewhere to determine the highest used number (either in PHP or SQL), because ORDER BY would not get the right result.

This topic has been dead for over six months. Start a new discussion instead.
Have something to contribute to this discussion? Please be thoughtful, detailed and courteous, and be sure to adhere to our posting rules.