Generate unique username from first and last name?

I've got a bunch of users in my database and I want to reset all their usernames to the first letter of their first name, plus their full last name. As you can imagine, there are some dupes. In this scenario, I'd like to add a "2" or "3" or something to the end of the username. How would I write a query to generate a unique username like this?

UPDATE user
SET username=lower(concat(substring(fi开发者_如何学Gorst_name,1,1), last_name), UNIQUETHINGHERE)

CREATE TABLE bar LIKE foo;

INSERT INTO bar (id,user,first,last)
(SELECT f.id,CONCAT(SUBSTRING(f.first,1,1),f.last,
     (SELECT COUNT(*) FROM foo f2
         WHERE SUBSTRING(f2.first,1,1) = SUBSTRING(f.first,1,1)
               AND f2.last = f.last AND f2.id <= f.id
         )),f.first,f.last from foo f);

DROP TABLE foo;
RENAME TABLE bar TO foo;

This relies on a primary key id, so for each record inserted into bar, we only count duplicates found in foo with id less than bar.id.

Given foo:

select * from foo;
+----+------+--------+--------+
| id | user | first  | last   |
+----+------+--------+--------+
|  1 | aaa  | Roger  | Hill   | 
|  2 | bbb  | Sally  | Road   | 
|  3 | ccc  | Fred   | Mount  | 
|  4 | ddd  | Darren | Meadow | 
|  5 | eee  | Sharon | Road   | 
+----+------+--------+--------+

The above INSERTs into bar, resulting in:

select * from bar;
+----+----------+--------+--------+
| id | user     | first  | last   |
+----+----------+--------+--------+
|  1 | RHill1   | Roger  | Hill   | 
|  2 | SRoad1   | Sally  | Road   | 
|  3 | FMount1  | Fred   | Mount  | 
|  4 | DMeadow1 | Darren | Meadow | 
|  5 | SRoad2   | Sharon | Road   | 
+----+----------+--------+--------+

To remove the "1" from the end of user names,

INSERT INTO bar (id,user,first,last)
(SELECT f3.id,
        CONCAT(
            SUBSTRING(f3.first,1,1),
            f3.last,
            CASE f3.cnt WHEN 1 THEN '' ELSE f3.cnt END),
        f3.first,
        f3.last
 FROM (
    SELECT
        f.id,
        f.first,
        f.last,
        (
            SELECT COUNT(*) 
            FROM foo f2
            WHERE SUBSTRING(f2.first,1,1) = SUBSTRING(f.first,1,1)
            AND f2.last = f.last AND f2.id <= f.id
        ) as cnt
    FROM foo f) f3)

As a two-parter:

SELECT max(username)
FROM user
WHERE username LIKE concat(lower(concat(substring(first_name,1,1),lastname), '%')

to retrieve the "highest" username for that name combo. Extract the numeric suffix, increment it, then insert back into the database for your new user.

This is racy, of course. Two users with the same first/last names might stomp on each other's usernames, depending on how things work out. You'd definitely want to sprinkle some transaction/locking onto the queries to make sure you don't have any users conflicting.

Nevermind.... I just found the dupes:

select LOWER(CONCAT(SUBSTRING(first_name,1,1),last_name)) as new_login,count(* ) as cnt from wx_user group by new_login having count(* )>1;

And set those ones manually. Was only a handful.

Inspired in the answer of unutbu: there is no need to create an extra table neither several queries:

UPDATE USER a
    LEFT JOIN (
        SELECT USR_ID, 
            REPLACE(
                CONCAT(
                    SUBSTRING(f.`USR_FIRSTNAME`,1,1),
                    f.`USR_LASTNAME`,
                    (
                        (SELECT IF(COUNT(*) > 1, COUNT(*), '')
                            FROM USER f2
                            WHERE SUBSTRING(f2.`USR_FIRSTNAME`,1,1) = 
                                SUBSTRING(f.`USR_FIRSTNAME`,1,1)
                                AND f2.`USR_LASTNAME` = f.`USR_LASTNAME`
                                AND f2.`USR_ID` <= f.`USR_ID`)
                        )
                ),
                ' ',
                '') as login
        FROM USER f) b
    ON a.USR_ID = b.USR_ID
    SET a.USR_NICKNAME = b.login