CODEWITHSUNDEEP
sqlsql-update
user12074577
user12074577

Reputation: 1419

Updating Column Values Based on Column Average

I am trying to curve up the gpa of students in a table up by 0.3 points if they are below the avg gpa score of the students in the table. How would I even do something like that? Examples would be appreciated. I have a table called score with columns name and gpa. All I know is I would probably have to use update. I am new to SQL as well sorry for the type of question...

Upvotes: 1

Views: 672

Answers (1)

Tyler Morrow
Tyler Morrow

Reputation: 961

SELECT S.name, S.gpa, C.avgGPA AS AverageGPA,
    CASE S.gpa < C.avgGPA
        WHEN true THEN (S.gpa + 0.3)
        ELSE S.gpa
    END as CurvedGPA
FROM StudentGrades AS S JOIN (
        SELECT avg(gpa) AS avgGPA
        FROM StudentGrades
    ) AS C

Inner-most query finds the avg GPA on the table. Assumes datatype for gpa as decimal. Second inner-most query selects all the data so you can see everything compared to verify the results AND does the logic to curve it. Tested on mySQL Workbench but the syntax should work on Oracle. The above example is safe and doesn't modify the data, BUT you asked for an update so...

The answer to your question (not safe if ran multiple times):

UPDATE StudentGrades AS S JOIN (
    SELECT avg(gpa) AS avgGPA
    FROM StudentGrades
) AS C
SET S.gpa = S.gpa + 0.3
WHERE S.gpa < C.avgGPA

It might yell at you about no primary key in the WHERE clause but just ignore it. Good luck! This was fun ;)

Upvotes: 1

Related Questions