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.

Do you need this every time a new user is inserted, or just for one initial set? You can use an UPDATE to concatenate the id with admin. — Michael Berkowski
– Michael Berkowski, Commented Jan 25, 2013 at 14:20
I won't based on the id but MAX(SUBSTRING_INDEX(username,'admin',-1))+1. Is that possible? — Idham Perdameian
– Idham Perdameian, Commented Jan 25, 2013 at 14:21

Ray Z · Accepted Answer · 2013-01-25 14:44:45Z

6

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

edited Jan 25, 2013 at 14:44

answered Jan 25, 2013 at 14:24

Ray Z

1285 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Justin · Accepted Answer · 2013-01-25 14:29:37Z

1

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 |

answered Jan 25, 2013 at 14:29

Justin

9,7336 gold badges38 silver badges49 bronze badges

Collectives™ on Stack Overflow

mysql concat from select result

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related