4

I have student table with admission id, i want to add prefix to that admission id when i registered

Std Table:

S_AdminID  S_Name S_Gender
1          eeee   M
2          gggg   F

14AD is added to asdmission id I want like this 

S_AdminID      S_Name   S_Gender
14AD1          eeee     M
14AD2          gggg     F

Thanks in advance

Rahul Gupta
  • 9,775
  • 7
  • 56
  • 69
GuruKumar
  • 61
  • 1
  • 10

1 Answers1

2
UPDATE table set S_AdminID=CONCAT("14AD",S_AdminID)

But that may effect if your table attribute is of int or autoincrement

Demo

If you want it automatically then you can use Triggers

Example:

CREATE TRIGGER test BEFORE INSERT ON table_name
FOR EACH ROW SET @S_AdminID = CONCAT('14AD' ,@S_AdminID);

If you want to use prefix with Primary key , Auto increment. Then you have to make some change in table schema like:

CREATE TABLE myItems (
    id INT NOT NULL AUTO_INCREMENT,
    prefix CHAR(30) NOT NULL,
    PRIMARY KEY (id, prefix),
Community
  • 1
  • 1
Manwal
  • 23,450
  • 12
  • 63
  • 93
  • i dont want to update, during registration itself prefix should be added . – GuruKumar Jul 17 '14 at 04:43
  • well then you should show the INSERT query that works on registration – Hanky Panky Jul 17 '14 at 04:44
  • Insert into Std_table(S_Name,S_Gender) values('gggg','F') – GuruKumar Jul 17 '14 at 04:50
  • @GuruKumar you can do this is server side scripting language **PHP** or using **Trigger**. – Manwal Jul 17 '14 at 04:51
  • when i execute this query , s_admissionid is a primary key and auto increment Output should be 14AD005 gggg F 14AD006 tyht M like this i want – GuruKumar Jul 17 '14 at 04:52
  • CREATE TABLE table1_seq ( id INT NOT NULL AUTO_INCREMENT PRIMARY KEY ); CREATE TABLE table1 ( id VARCHAR(7) NOT NULL PRIMARY KEY DEFAULT '0', name VARCHAR(30) ); Now the trigger DELIMITER $$ CREATE TRIGGER tg_table1_insert BEFORE INSERT ON table1 FOR EACH ROW BEGIN INSERT INTO table1_seq VALUES (NULL); SET NEW.id = CONCAT('LHPL', LPAD(LAST_INSERT_ID(), 3, '0')); END$$ DELIMITER ; Then you just insert rows to table1 INSERT INTO Table1 (name) VALUES ('Jhon'), ('Mark'); And you'll have | ID | NAME | ------------------ | LHPL001 | Jhon | | LHPL002 | Mark | – GuruKumar Jul 17 '14 at 05:44