MySql - Inserting into the table in a while loop

Question

I have a takes table, and i want to convert each grade record to the numeric value and store it in the grade_point table .

I initialized the grade_point as:

CREATE TABLE IF NOT EXISTS university.grade_point (
    id VARCHAR(5),
    grade VARCHAR(2),
    grade_value NUMERIC(2 , 1)
);

I can approach the problem as in the following:

• Get id from takes table

• Get grade from takes table

• If grade is A then grade_value is 4.0

  If grade is A- then grade_value is 3.7 ...

I can write the following query:

insert into grade_point(id, grade, grade_value) 
SELECT 
    id,
    grade,
    CASE
        WHEN grade LIKE 'A' THEN 4.0
        WHEN grade LIKE 'A-' THEN 3.7
        WHEN grade LIKE 'B+' THEN 3.3
        WHEN grade LIKE 'B' THEN 3.0
    END AS grade_value
FROM
    takes;

I wonder if I can do that using procedure?

Inspired from the accepted solution of How can I loop through all rows of a table (MySQL):

• For each grade record in the takes table

  • Select the ith row

  • Get id, grade and convert grade to grade_value

  • insert it to the grade_point

• Result is:

delimiter 
create procedure update_grade_point()
begin
declare n int default 0
declare i int default 0
select count(grade) from takes into n    
set i = 0
while i < n do
    insert into grade_point(id, grade, grade_value) 
    select id, grade, 
        case
            when grade like 'A' then 4.0
            when grade like 'A-' then 3.7
            when grade like 'B+' then 3.3
            when grade like 'B' then 3.0
        end as grade_value
    from
        takes
    limit i, 1;
    set i = i + 1
end while
end;
delimiter ;

However, the above query responsed as:

0 row(s) affected, 1 warning(s): 1050 Table 'grade_point' already exists

When drop the grade_point table and re-execute the query i have the following response

0 row(s) affected

Nothing is inserted into the grade_point table.

Could you please tell me what I'm missing or wrong in my procedure?

Regards.

Answer 1

I don't know, why you want to do this, when a simpe insert is enough

But ij a stored procedure you would use a loop

CREATE TABLE IF NOT EXISTS  grade_point (
    id VARCHAR(5),
    grade VARCHAR(2),
    grade_value NUMERIC(2 , 1)
);

CREATE TABLE IF NOT EXISTS takes (
    id VARCHAR(5),
    grade VARCHAR(2)
);

INSERT INTO takes VALUES ("1","A"),("2","B"),("3","A"),("4","B")

CREATE PROCEDURE update_grade_point (

)
BEGIN
  DECLARE finished INTEGER DEFAULT 0;
  DECLARE _grade varchar(2) DEFAULT "";
  DECLARE _id varchar(5) DEFAULT "";

  -- declare cursor for employee email
  DEClARE curtakes 
      CURSOR FOR 
          SELECT id,grade FROM takes;

  -- declare NOT FOUND handler
  DECLARE CONTINUE HANDLER 
        FOR NOT FOUND SET finished = 1;

  OPEN curtakes;

  getidGrade: LOOP
      FETCH curtakes INTO _id,_grade;
      IF finished = 1 THEN 
          LEAVE getidGrade;
      END IF;
                insert into grade_point(id, grade, grade_value) VALUES (_id,_grade,        case
            when grade like 'A' then 4.0
            when grade like 'A-' then 3.7
            when grade like 'B+' then 3.3
            when grade like 'B' then 3.0
        end);
  END LOOP getidGrade;
  CLOSE curtakes;

END

CALL update_grade_point()

SELECT * FROM grade_point

id | grade | grade_value
:- | :---- | ----------:
1  | A     |         4.0
2  | B     |         3.0
3  | A     |         4.0
4  | B     |         3.0

db<>fiddle here