How to set username using id in mysql

Question

I have a database table in mysql

create table userstable
(
    id int not null auto_increment,
    name varchar(80) not null,
    username  varchar(80) not null,
    primary key(id)
);

How to add new row in mysql database, so that username will be 'name'+('id'*100)

Example :

ID  name    username

1   A       A100
2   B       B200
3   C       C300
4   user    user400

You need to use triggers for that. Create a AFTER INSERT trigger for that table — Arun Krish, Jul 31 '15 at 08:54
Just to confirm what's obvious from the DDL: You are fine with duplicates in `username`. e.g. `(1,A1,A1100)` and `(11,A,A1100)` — vhu, Jul 31 '15 at 09:20

Arun Krish · Accepted Answer · 2015-07-31T10:14:45.363

2

You need trigger for that process. Create the following trigger

CREATE TRIGGER username_change 
BEFORE INSERT ON userstable 
FOR EACH ROW 
BEGIN
 SET NEW.username = CONCAT(NEW.name,(NEW.id*100)); 
END

OR

INSERT INTO userstable (id,name, username) VALUES (2, 'B', CONCAT(name,(id*100)));

Try this.

edited Jul 31 '15 at 10:14

answered Jul 31 '15 at 09:07

Arun Krish

2,153
1
10
15

Thanks for your answer,it's work, but can i solve this using subquery / query without using trigger? – Divyang Jul 31 '15 at 09:21
@Divyang - Check the insert query I have given. – Arun Krish Jul 31 '15 at 10:15

score 0 · Answer 2 · edited May 23 '17 at 10:26

0

You'll need to write a trigger or update the field after insertion. A similar question provides more insight:

Can you access the auto increment value in MySQL within one statement?

edited May 23 '17 at 10:26

Community

1
1

answered Jul 31 '15 at 09:03

LeDoc

935
2
12
24

score 0 · Answer 3 · answered Jul 31 '15 at 09:22

As @ArunKrish correctly pointed out, you may use TRIGGER to update the data as part of the insert. Another option is to use view:

CREATE VIEW v1 AS 
 SELECT id,name,CONCAT(name,id*100) AS username FROM userstable;

You may also use the query as-is, without view:

SELECT id,name,CONCAT(name,id*100) AS username FROM userstable;

How to set username using id in mysql

3 Answers3