duaevb1511
2014-08-07 11:00
浏览 22
已采纳

mysql根据另一个表将序列更新到一个表中

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.

  • 写回答
  • 关注问题
  • 收藏
  • 邀请回答

1条回答 默认 最新

  • douyingmou1389 2014-08-07 11:33
    已采纳

    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
    

    Hope this helps.

    打赏 评论

相关推荐 更多相似问题