CODEWITHSUNDEEP
pythonsqlite
eclipsedlamp
eclipsedlamp

Reputation: 149

sqlite - Insert based on field comparison on other table

I have a table student_pop that has the following data:

 ID  GPA  LETTER
 1    84        
 2    92       
 3    55

I have another table grade_info that has the following data:

lettergrade MinimumGrade MaximumGrade
   A             93         100
   A-            90         92
   B+            87         89
   B             83         86
   B-            80         82
   C+            77         79
   C             73         76
   C-            70         72
   D+            67         69
   D             63         66
   D-            60         62
   E              0         60

I am looking to insert a letter grade based on the GPA and the data from the grade_info table.

I think it would go something like this:

INSERT INTO student_pop (letter)
SELECT lettergrade
from grade_info
WHERE(select GPA from student_pop WHERE GPA >=(select MinimumGrade from grade_info) and GPA <=(select MaximumGrade from grade_info)

I have the logic of what needs to happen, but I can't seem to workout the correct INSERT syntax.

Upvotes: 0

Views: 33

Answers (1)

forpas
forpas

Reputation: 164064

You must UPDATE the table:

update student_pop
set letter = (
  select lettergrade
  from grade_info
  where student_pop.gpa between minimumgrade and maximumgrade
);

See the demo.
Results:

| ID  | GPA | LETTER |
| --- | --- | ------ |
| 1   | 84  | B      |
| 2   | 92  | A-     |
| 3   | 55  | E      |

Upvotes: 1

Related Questions