Geek asks and answers

Mysql concat from selected result

I would like to insert data into MySQL with an automatic name in the field username, but how can I do this ?.
Currently, the data at the table:

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

I am trying to use this sql but it cannot:

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

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

The final result I want:

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

perhaps? thank.

+5
source share
2 answers

, . , : http://www.roseindia.net/sql/trigger/mysql-trigger-after-insert.shtml

Edit

, MySQL , .

, :

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

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))
);

:

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

More articles:


All Articles