mysql concat from select result

Question

I would like to insert data in MySQL with automatic naming on field username but how i can do it?.
Currently data at table is:

+----+----------+
| id | username |
+----+----------+
|  1 | admin1   |
|  2 | admin2   |
+----+----------+

I try using this sql but it's can't:

INSERT INTO `tbl_user` (
`username`
)
VALUES (
CONCAT('admin',(SELECT MAX(SUBSTRING_INDEX(`username`,'admin',-1))+1 FROM `tbl_user`))
);

and get error message #1093 - You can't specify target table 'tbl_user' for update in FROM clause

Final result i want is:

+----+----------+
| id | username |
+----+----------+
|  1 | admin1   |
|  2 | admin2   |
|  6 | admin3   |
|  9 | admin4   |
+----+----------+

is that possible? thanks.

Answer 1

You can use a trigger that would update the column username after an insert. Here's some more information on how to actually do this: http://www.roseindia.net/sql/trigger/mysql-trigger-after-insert.shtml

Edit

I forgot that MySQL won't allow you to update a table from a trigger declared on the same table.

However, this should do what you're trying to do:

SET @id := (SELECT id FROM YOUR_TABLE ORDER BY id DESC LIMIT 1);
INSERT INTO YOUR_TABLE (username) VALUES(
   CONCAT("ADMIN", @id + 1)
);

Answer 2

Query:

SQLFIDDLEExample

INSERT INTO `tbl_user` (
`username`
)
VALUES (
CONCAT('admin',(SELECT MAX(CAST(REPLACE(`username`,'admin','') AS SIGNED INTEGER))+1 
                FROM (SELECT * FROM tbl_user) t))
);

Result:

|     ID | USERNAME |
---------------------
|      1 |   admin1 |
|      2 |   admin2 |
| (null) |   admin3 |