mysql update sequence into one table depending on another table

Below are the 2 tables:

classes

            academic_year_id    student_id      standard

                    2               1               10
                    2               2               10
                    2               3               10
                    2               4               10
                    2               5               10
                    2               6               10
                    2               7               11 Science
                    2               8               11 Science

Here standard is not INT

student

            student_id      roll_no         name            teacher_approval

                1               0       S Sumeet G                  1
                2               0       Nair Nikhil R               1
                3               0       Nayak Ankita R              0
                4               0       Rathod Ketan P              0
                5               0       Patel Vishal D              1
                6               0       Patel Jignesh R             0
                7               0       Prajapati Bhavesh A         1
                8               0       Shah Harsh N                1

What i want to do:

when teacher selects standard -> 10 and press a button “Assign Roll No”

i want all student of 10th standard to be given roll no sequentially order by name and which are approved by teacher that is teacher_approval = ‘1’

So my student table becomes as below:

student

            student_id      roll_no         name            teacher_approval

                1               3       S Sumeet G                  1
                2               1       Nair Nikhil R               1
                3               0       Nayak Ankita R              0
                4               0       Rathod Ketan P              0
                5               2       Patel Vishal D              1
                6               0       Patel Jignesh R             0
                7               0       Prajapati Bhavesh A         1
                8               0       Shah Harsh N                1

I have so far tried below code:

$standard = $_POST['standard']

SET @incr = 0

     UPDATE
        student AS st
        JOIN
        (   SELECT * FROM
        classes AS classes
        WHERE standard = '".$standard."'
        ) AS tmp
        ON tmp.student_id = st.id
        SET
        st.roll_no = @incr:=@incr+1 
        WHERE st.teacher_approval = '1'
        ORDER BY st.name ASC

But it gives me an error: incorrect usage of update and order by

Anyone with a similar experience? Any help would be appreciated. Thanks.

Answer

In MySql, you can’t have an ORDER BY as part of the UPDATE directly when using multiple tables (see this link). Try this instead :

UPDATE student AS table1
        INNER JOIN (
        SELECT st.student_id, st.roll_no, st.name, st.teacher_approval
        FROM student AS st
            JOIN (
            SELECT * FROM
            classes AS cl
            WHERE cl.standard = '".$standard."'
            ) AS tmp
            ON tmp.student_id = st.student_id
            WHERE st.teacher_approval = '1'
            ORDER BY st.name ASC
        ) AS table2 
        ON table2.student_id = table1.student_id
    SET table1.roll_no = @incr:=@incr+1