SQL- CONSTRAINT

user_13074670 0
Viewed 55

Is it possible to create a table that automatically update a column value depending on another column value?

For example, I need to create a table of GRADES:

GRADES-> STUDENT_ID GRADE1 GRADE2 GRADE3 FINAL_GRADE

I want the the value in the final grade will be equal to the last grade that is not null. for example, 

(studentid = 12345 grade1 = null grade2 = null grade3 = null finalgrade =null)
(studentid = 12345 grade1 = 80 grade2 = null grade3 = null finalgrade =80)
(studentid = 12345 grade1 = 80 grade2 = 75 grade3 = null finalgrade =75)
(studentid = 12345 grade1 = 80 grade2 = 75 grade3 = 90 finalgrade =90)
constraints oracle sql
1 Answers

Yes, there is something called a virtual column:

alter table grades
add final_grade generated always as (coalesce(grade3,grade2,grade1));

The coalesce function returns the first of its arguments that is not null.

Note that you cannot insert or update values in a virtual column, they are computed by the database.

user_187470
Related