create Student ID number with year and auto incremented value

Question

i created a student id number compose of text and auto incremented value like: KP0001 - KP9999 it will reset to KP0001 again, It will result to duplicate ID number. I changed it to this: 2013-00001 but it's not working i dont know why.

this is what i tried:

text  id
KP    1
KP    2
KP    3

SELECT * ,concat(text,LPAD(id,4,'0')) as stud_id FROM students

i want to change the text into year which will update yearly. Thanks, Mills

post some schema , sample data , desired out put and what you have tried. — Muhammad Raheel, Mar 06 '13 at 08:41
Well take a look at [this](http://stackoverflow.com/questions/15033079/update-query-with-left-join-variable-and-autoincrement-using-mysql). You might want something similar — Muhammad Raheel, Mar 06 '13 at 08:54

Muhammad Raheel · Answer 1 · 2013-03-06T10:32:28.460

0

Try this

SELECT
  *,
  concat(YEAR(NOW()),'-',LPAD(id,5,'0')) as stud_id
FROM students

SQL Fiddle Demo

OUTPUT

id  text    stud_id
--------------------
1   KP      2013-00001
2   KP      2013-00002
3   KP      2013-00003

You can use this update query

UPDATE students AS s
LEFT JOIN(
        SELECT 
        *
        FROM students
    ) AS l ON l.id = s.id
SET s.text = concat(YEAR(NOW()),'-',LPAD(l.id,5,'0'))

edited Mar 06 '13 at 10:32

answered Mar 06 '13 at 08:58

Muhammad Raheel

19,823
7
67
103

how about when i insert the current year? – user2089597 Mar 06 '13 at 09:01
i don't understand what you mean. Please tell me clearly. Also see NOW() is selecting current datetime and YEAR() is extracting year from it – Muhammad Raheel Mar 06 '13 at 09:03
i mean i want to insert the current year in the database – user2089597 Mar 06 '13 at 09:43

create Student ID number with year and auto incremented value

1 Answers1

SQL Fiddle Demo