Update and renew data based on data in other tables-CodePudding

There are 3 tables student, course, and takes as following

CREATE TABLE student
(
     ID         varchar(5), 
     name       varchar(20) NOT NULL, 
     dept_name  varchar(20), 
     tot_cred   numeric(3,0) CHECK (tot_cred >= 0),
     PRIMARY KEY (ID),
     FOREIGN KEY (dept_name) REFERENCES department
        ON DELETE SET NULL
)

CREATE TABLE takes
(
     ID         varchar(5), 
     course_id  varchar(8),
     sec_id     varchar(8), 
     semester   varchar(6),
     year       numeric(4,0),
     grade      varchar(2),
     PRIMARY KEY (ID, course_id, sec_id, semester, year),
     FOREIGN KEY (course_id, sec_id, semester, year) REFERENCES section
        ON DELETE CASCADE,
     FOREIGN KEY (ID) REFERENCES student
        ON DELETE CASCADE
    )

CREATE TABLE course
(
    course_id   varchar(8), 
    title       varchar(50), 
    dept_name   varchar(20),
    credits     numeric(2,0) CHECK (credits > 0),

    PRIMARY KEY (course_id),
    FOREIGN KEY (dept_name) REFERENCES department
        ON DELETE SET NULL
)

tot_cred column data in the student table now is assigned with random values (not correct), I want to perform the query that updates and renews those data based on the course's grade each student has taken. For those students who received F grade will be excluded and those who didn't take any course will be assigned 0 as tot_cred.

I came up with two approaches, one is

UPDATE student 
SET tot_cred = (SELECT SUM(credits)  
                FROM takes, course  
                WHERE takes.course_id = course.course_id 
                  AND student.ID = takes.ID 
                  AND takes.grade <> 'F' 
                  AND takes.grade IS NOT NULL)

This query meets all my needs, but for those students who didn't take any course, it does assign NULL value instead of 0.

The second is using case when

UPDATE student
SET tot_cred = (select sum(credits)
    case 
         when sum(credits) IS NOT NULL then sum(credits)
         else 0 end
    FROM takes as t, course as c
    WHERE t.course_id = c.course_id
    AND t.grade<>'F' and t.grade IS NOT NULL
)

But it assigned 0 to all students. Is any way to achieve the above requirement?

CodePudding user response：

If the 1st query meets your requirement and the only problem is that it returns NULL for the students that did not take any course then the easiest solution would be to use instead of SUM() aggregate function the function TOTAL() which will return 0 instead of NULL:

UPDATE student AS s 
SET tot_cred = (
  SELECT TOTAL(c.credits)  
  FROM takes t INNER JOIN course c 
  ON t.course_id = c.course_id 
  WHERE t.ID = s.ID AND t.grade <> 'F' AND t.grade IS NOT NULL
);

The same could be done with COALESCE():

SELECT COALESCE(SUM(credits), 0)...

Also, use a proper join with an ON clause and aliases for the tables to improve readability.