mysql - Using Subquery to update total column -

here query:

update student_tests,   (select sum(olc_sta_i_points_earned) total, olc_sta_i_stt_num student_answers      join student_tests on olc_sta_i_stt_num = olc_stt_i_num            ) set student_tests.olc_stt_i_score = a.total a.olc_sta_i_stt_num = student_tests.olc_stt_i_num  

there no errors says 0 rows affected.

basically have 2 tables: student_tests , student_answers test id mapped student_answers tables. want subquery can sum student answers specific test id , update score column in student_tests table in tests table.

am doing wrong clause here? or else?

you should phrase update/join explicitly, rather having join condition in where clause.

your problem have no group by in subquery. join student_tests seems unnecessary, try this:

update student_tests s join        (select sum(a.olc_sta_i_points_earned) total, a.olc_sta_i_stt_num         student_answers              group a.olc_sta_i_stt_num            )        on a.olc_sta_i_stt_num = t.olc_stt_i_num      set s.olc_stt_i_score = a.total