CODEWITHSUNDEEP
sql
WhatWhatWhat
WhatWhatWhat

Reputation: 103

Populate a column based on another column

I'm trying to make a table for Exams where it looks like:

ExamID | Name | Points | Grade

Where Points is equal to some value from 0 to 100. Now I want to automatically set the value of Grade based on the value of Points. IF > 90 Grade=A...

What is the basic syntax for an sql query to do this? I'll adjust it to my needs afterwards anyway.

Tried playing around with the CASE syntax, but couldn't do it. I'm guessing I should use the UPDATE query, not the INSERT query.

Upvotes: 10

Views: 47237

Answers (4)

szakwani
szakwani

Reputation: 384

You need to use the UPDATE below find the syntax.You must write the code on the curly braces as given on editor

update [Tablename] set Grade = 'A' where points >90;

update Examgrades set Grade = 'A' where points >90;

Upvotes: 0

szakwani
szakwani

Reputation: 384

Build schema first

**TABLE**

CREATE TABLE Examgrades 
    (
     Examid int auto_increment primary key, 
     Name varchar(20), 
     points int,
     Grade varchar(1)
    );

**INSERT RECORDS**

INSERT INTO Examgrades
(Name, points)
VALUES
('Email', 91),
('Twitter', 80);

**YOUR UPDATE**

update  Examgrades
set Grade = 'A'
where points >90;

http://sqlfiddle.com/#!2/d3fd3f/1

Upvotes: -3

Jcl
Jcl

Reputation: 28272

You could use a computed column. Once your table is created without the Grade column, you could add a computed column like this (this should work at least on SQL Server, but you might need to find the differences on other DBMS's sql):

ALTER TABLE Exams
  ADD Grade AS
    CASE
      WHEN (Points > 90) THEN 'A'
      WHEN (Points > 70) THEN 'B'
      WHEN (Points > 50) THEN 'C'
      ELSE 'D'
    END

Depending on your DBMS you might want to use a Trigger for both insertion and updating instead, but the syntax for triggers is largely dependent on the system you are using.

If the Grade is only calculated and you don't really need to store it, you could use a View instead, or directly create it when querying the table and just not have a Grade column at all.

Upvotes: 7

Dharmesh Porwal
Dharmesh Porwal

Reputation: 1406

Try this demo code for your desired result this is an update into your table, its written in oracle sql, also you can write function and call it when updating grade :- 

 create table examd (examid number,name varchar2(20),points number, grade varchar2(2))

 insert all 
 into examd(examid,name,points) values(330,'MILEY',80)
 into examd(examid,name,points) values(766,'CYRUS',70)
 into examd(examid,name,points) values(450,'GEORGE',30)
 into examd(examid,name,points) values(330,'WITCHER',99)
 into examd(examid,name,points) values(888,'WALKER',50)
 into examd(examid,name,points) values(233,'SHRIN',73)
 into examd(examid,name,points) values(330,'LILY',80)
 select * from dual;

 commit;

 select * from examd;

       update examd ex 
       set ex.grade=
                    (select case when ex1.points>=0 and ex1.points<=34 then 'F' 
                     else 
                     case when ex1.points>=35 and ex1.points<=49 then 'E' 
                     else 
                     case when ex1.points>=50 and ex1.points<=69 then 'D' 
                     else  
                     case when ex1.points>=70 and ex1.points<=80 then 'C' 
                     else 
                     case when ex1.points>=81 and ex1.points<=90 then 'B' 
                     else
                     case when ex1.points>=91 and ex1.points<=95 then 'A' 
                     else  
                     case when ex1.points>=96 and ex1.points<=100 then 'A+' 
                     end end end end end end end grade
                        from examd ex1 where ex1.examid=ex.examid and
                         ex1.points=ex.points 
                          and ex1.name=ex.name);

     commit;

Upvotes: 0

Related Questions