updating table with the result of a query

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

updating table with the result of a query

Author

Topic

sadiakhattak
Starting Member

4 Posts

Posted - 2011-06-08 : 02:21:31

hi
i need to update my table with the result of a query. i m using the following query that calculates gpa (per semester) of a student.

;with a as
(
select
student_id, semester_id, subject_id, subject_grade,Credit_Hours,
row_number() over (partition by semester_id,subject_id order by subject_grade desc) as rownum
from
Marks
)
select student_id,semester_id,(sum(subject_grade)/sum(Credit_Hours)) as gpa from a
where rownum = 1
group by student_id,semester_id

for example i have a student with roll no 11 then the gpaz for 4 semesters is
student_id semester_id gpa
11 1 2.53
11 2 3.00
11 3 3.56
11 4 3.00

what i need is to update my table marks column gpa such that gpa for each semester of that student is updated from the result of the above query n i dont know how to do that how to include update query with the above query. can someone give me solution for that? thanks

jfarrugia
Yak Posting Veteran

55 Posts

Posted - 2011-06-08 : 09:12:42

May i suggest you insert results in a #temp table then create the update accordingly? its simpler to manage

Where software development knowledge meets the reader

Seventhnight
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2011-06-08 : 09:31:58

[code]
;With A As (
Select
Student_Id,
Semester_Id,
Subject_Id,
Subject_Grade,
Credit_Hours,
RowNum = Row_Number() Over (Partition By Semester_Id, Subject_Id Order By Subject_Grade Desc)
From Marks
)

Update Z
Set
gpa = Y.gpa
From yourTable Z
Inner Join
(
Select
Student_Id,
Semester_Id,
gpa = (sum(Subject_Grade)/sum(Credit_Hours))
from A
where RowNum = 1
group by Student_Id, Semester_Id
) Y
On Z.Student_Id = Y.Student_Id
and Z.Semester_Id = Y.Semester_Id
[/code]

Corey

I Has Returned!!

sadiakhattak
Starting Member

4 Posts

Posted - 2011-06-09 : 10:46:33

Thank You so Much for the help. it worked :D
GOD bless you both

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources