CODEWITHSUNDEEP
mysqlsql
Vaibhav
Vaibhav

Reputation: 215

Is it possible to set a column value as per the value in another column?

Let's say I'm making a 'students' database and there's a table called 'exams' in it. Now I have the following two columns- 'marks' and 'grade' in it and I want the 'grade' column to automatically receive a value based on the value entered in 'marks'. Say 'A' for 'marks'>=90, 'B' for 'marks'>=80 and so on. Is there a way to do that?

Upvotes: 1

Views: 266

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269513

Not really easily. In MySQL, you can do an update:

update students
    set grade = (case when marks >= 90 then 'A'
                      when marks >= 80 then 'B' 
                      . . .
                 end);

But that will be out-of-date with the next insert or update statement.

In MySQL 5.7.6, you can have the column generated automatically:

alter table students
    add grade char(1) as (case when marks >= 90 then 'A'
                               when marks >= 80 then 'B' 
                               . . .
                          end);

This is convenient, because the column is calculated at query time so it is automatically up-to-date.

In earlier versions, you can do something similar with a view:

create view v_students as
    select s.*,
           (case when marks >= 90 then 'A'
                 when marks >= 80 then 'B' 
                 . . .
            end) as grade
    from students;

This is almost as good as the generated column, but it requires that everyone query the view.

And finally, you can actually store the value, using triggers to maintain it when marks changes.

Upvotes: 5

Related Questions