vb4Dr2i8

1CREATE TABLE IF NOT EXISTS tmp_student ( personid INT UNSIGNED NOT NULL PRIMARY KEY, new_studentid CHAR( 15 ) NOT NULL );
2TRUNCATE TABLE tmp_student;
3LOCK TABLE student WRITE, tmp_student WRITE;
4SELECT
5    @max_id := IFNULL( MAX( studentid ) , 0 )
6FROM
7    student
8WHERE studentid LIKE
9            CONCAT(
10                      'G'
11                    , DATE_FORMAT( CURDATE(), '%y')
12                    , '____'
13                    , IF( MONTH( CURDATE() ) > 9, CHAR( 55 + MONTH( CURDATE() ) ), MONTH( CURDATE() ) ), DAY( CURDATE() )
14                )
15;
16
17INSERT INTO tmp_student
18    SELECT personid
19        , CONCAT(
20              'G'
21            , DATE_FORMAT( NOW(), '%y')
22            , LPAD( ( @max_id := @max_id + 1 ), 4, '0' )
23            , IF( MONTH( CURDATE() ) > 9, CHAR( 55 + MONTH( CURDATE() ) ), MONTH( CURDATE() ) )
24            , DAY( CURDATE() )
25        ) AS new_student_id
26    FROM student
27    WHERE studentid = 0
28;   
29
30UPDATE student JOIN tmp_student USING ( personid ) SET studentid = new_studentid;
31
32UNLOCK TABLES;