sql update table from a join query

Question

hi I have table as below named enrolment, I need to update the null averagemark from the data from another table so that the average of all assignments done by a student will populate the field. Can I anyone advise how to do this using Isql as the dbms?

enrolment-    
student_id- course code - averagemark 
    1              a1           -   0
    2              b2           -   0
    3              c3           -   0
    4              d4           -   0

assignment-
    student_id- course code -      assignment number    mark 
        1              a1                  1            -   50
        1              b2                  2            -   55
        2              a1                  1            -   60
        2              b2                  2            -   65

Your query gives the average of the marks for a **course** of the student, not the average of the student in all of his courses. The table that you want to update, on the other hand, only has the student_id, so how are we supposed to update the average mark? — Lamak, Jul 04 '14 at 20:57
possible duplicate of [How can I do an UPDATE statement with JOIN in SQL?](http://stackoverflow.com/questions/1293330/how-can-i-do-an-update-statement-with-join-in-sql) — Black Frog, Jul 04 '14 at 21:10
hi yeah sorry my bad, the original table also has course_code in it, does that make sense now, apologies if this is a duplicate — user3783811, Jul 04 '14 at 21:42
point is (about that duplicate) that you will find the syntax for many dbms type there; and you will see that the syntax differs quite a lot. You have not told use what dbms your question relates to so, to answer we either have to guess one, or answer for every dbms we can think of. please indicate which dbms you are using. — Paul Maxwell, Jul 05 '14 at 02:04
hi using ISql as the DBMS I have updated the original question as I think I gave incorrect information, just gave the base tables if anyone could offer some help that would be great? — user3783811, Jul 06 '14 at 20:18

score 0 · Accepted Answer · edited May 23 '17 at 10:26

This example is based on Microsoft SQL Server. Copying the sample from How can I do an UPDATE statement with JOIN in SQL?:

update u
set u.assid = s.assid
from ud u
    inner join sale s on
        u.id = s.udid

Try something like this (providing the name of the column in the Student table is AverageMark)

UPDATE s
SET s.AverageMark = AVG(mark)
FROM Student s LEFT OUTER JOIN enrolment e
    on e.Student_Id = e.Student_Id
               LEFT OUTER JOIN assignment a
    ON e.student_id = a.student_id
AND e.course_code = a.course_code
GROUP BY e.student_id, e.course_code

sql update table from a join query

1 Answers1