I have a question. Suppose I have two tables. One table is details table and other one is summary table. E.g.
- TotalMarks.tbl, columns (Name, Surname, Total) --->Details table
- SubjectWiseMarks.tbl, columns (Name, Surname, Subject Name, Marks) ---> Summary table
Lets assume data in 2nd table (SubjectWiseMarks.tbl) as:
Name Surname SubjectName Marks
Sanjay Dutt English 85
Sanjay Dutt Hindi 70
Sanjay Dutt Mathematics 43
Sanjay Dutt Science 36
Sanjay Dutt Social Science 54
Now in the TotalMarks.tbl, My data would be
Name Surname TotalMarks
Sanjay Dutt 288
I am using below query to achieve this result:
update TotalMarks set Total = (select sum(Marks) from SubjectWiseMarks where name = 'Sanjay' and Surname = 'Dutt') where name = 'Sanjay' and Surname = 'Dutt';
Now my problem is to make this query iterative. I have so many students like Salman Khan, ShahRukh Khan, Ranbir Kapoor etc. I cannot keep updating for each one of them manually, one by one. I have full data in table 2 and want to update table one for each student by writing a single query, a loop of some sort. Please help me in this.