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
 Update a table

Author  Topic 

greenspacechunks
Starting Member

5 Posts

Posted - 2007-04-10 : 17:24:34
I am having problems with this gradebook database I am developing. I have a temporary table that contains all the category grades for each students (i.e. quiz, tests, and homework). However, I cannot just simply add these grades for the student's total grade to update in the CLASS table. I think I need to do something else besides a "while" loop because my loop is simply going through one time for the first student's class. I am new to SQL, so any help would be greatly appreciated! Thanks!

CREATE TABLE TEMP_TABLE(StudentID int NOT NULL, ClassID int NOT NULL, CatID int NOT NULL, Grade decimal(3,2) NOT NULL)
INSERT INTO TEMP_TABLE
(StudentID, ClassID, CatID, GRADE)
SELECT STUDENT.StudentID, CATEGORY.ClassID, CATEGORY.CategoryID, STUDENT_POINTS_CATEGORY.CategoryGrade * CATEGORY.Weight
FROM STUDENT INNER JOIN
STUDENT_POINTS_CATEGORY ON STUDENT.StudentID = STUDENT_POINTS_CATEGORY.StudentID INNER JOIN
CATEGORY ON STUDENT_POINTS_CATEGORY.CategoryID = CATEGORY.CategoryID

DECLARE @student_count int
DECLARE @class_count int
DECLARE @s int
DECLARE @c int
DECLARE @grade decimal(3,2)

SET @s = 2
SET @c = 1

SELECT @student_count = COUNT(DISTINCT StudentID)
FROM TEMP_TABLE
SELECT @class_count = COUNT(DISTINCT ClassID)
FROM TEMP_TABLE

WHILE(@s < @student_count)
BEGIN
SELECT @grade = SUM(Grade)
FROM TEMP_TABLE
WHERE StudentID = @s AND ClassID = @c

UPDATE STUDENT_CLASS
SET STUDENT_CLASS.PercentageGrade = @grade
WHERE StudentID = @s AND ClassID = @c

IF(@c > @class_count)
BEGIN
SET @c = 0
SET @s = @s + 1
END
SET @c = @c + 1
END

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-04-10 : 18:59:20
You don't need WHILE loop.

UPDATE c
SET PercentageGrade = TotalGrade
FROM STUDENT_CLASS c INNER JOIN
(
SELECT ClassID, StudentID, TotalGrade = SUM(Grade)
FROM TEMP_TABLE
GROUP BY ClassID, StudentID
) t
ON c.StudentID = t.StudentID
AND c.ClassID = t.ClassID



KH

Go to Top of Page

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-04-10 : 19:02:58
Is TEMP_TABLE really a temporary table or permanent table ?

Temporary table is created with table name prefix with '#'.
Like

CREATE TABLE #TEMP_TABLE
(
StudentID int NOT NULL,
ClassID int NOT NULL,
CatID int NOT NULL,
Grade decimal(3,2) NOT NULL
)


Looking at it, you don't need temp table at all. You can update STUDENT_CLASS directly from your "INSERT INTO TEMP_TABLE" query


KH

Go to Top of Page

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-04-10 : 19:14:06
Here is the combined query

UPDATE c
SET PercentageGrade = TotalGrade
FROM STUDENT_CLASS c INNER JOIN
(
SELECT ClassID, StudentID, TotalGrade = SUM(Grade)
FROM
(
SELECT STUDENT.StudentID, CATEGORY.ClassID, CATEGORY.CategoryID,
STUDENT_POINTS_CATEGORY.CategoryGrade * CATEGORY.Weight as Grade
FROM STUDENT
INNER JOIN STUDENT_POINTS_CATEGORY
ON STUDENT.StudentID = STUDENT_POINTS_CATEGORY.StudentID
INNER JOIN CATEGORY
ON STUDENT_POINTS_CATEGORY.CategoryID = CATEGORY.CategoryID

) t
GROUP BY ClassID, StudentID
) t
ON c.StudentID = t.StudentID
AND c.ClassID = t.ClassID



KH

Go to Top of Page

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-04-10 : 19:21:42
to further simplify it

UPDATE c
SET PercentageGrade = TotalGrade
FROM STUDENT_CLASS c INNER JOIN
(
SELECT STUDENT.StudentID, CATEGORY.ClassID, CATEGORY.CategoryID,
SUM(STUDENT_POINTS_CATEGORY.CategoryGrade * CATEGORY.Weight) AS TotalGrade
FROM STUDENT
INNER JOIN STUDENT_POINTS_CATEGORY
ON STUDENT.StudentID = STUDENT_POINTS_CATEGORY.StudentID
INNER JOIN CATEGORY
ON STUDENT_POINTS_CATEGORY.CategoryID = CATEGORY.CategoryID
GROUP BY STUDENT.StudentID, CATEGORY.ClassID
) t
ON c.StudentID = t.StudentID
AND c.ClassID = t.ClassID



KH

Go to Top of Page

SQLUSA
Starting Member

28 Posts

Posted - 2007-04-10 : 20:11:56
Nice Code!!!

In SQL Server 2000, instead of DERIVED TABLE, you can use a CTE!

Kalman Toth, Database, DW & BI Architect
SQL Server 2005 Training - http://www.sqlusa.com
Go to Top of Page

greenspacechunks
Starting Member

5 Posts

Posted - 2007-04-10 : 20:42:53
Actually, getting away from the computer made me realize that I don't even need an updated column. I should be using a view.
Go to Top of Page

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-04-10 : 21:08:47
quote:
Originally posted by SQLUSA

Nice Code!!!

In SQL Server 2000, instead of DERIVED TABLE, you can use a CTE!

Kalman Toth, Database, DW & BI Architect
SQL Server 2005 Training - http://www.sqlusa.com



You mean 2005 ?


KH

Go to Top of Page
   

- Advertisement -