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.

INSERT tablename (column1, column2, colmn3) VALUES (value1, value2, value3) is proper INSERT syntax. You aren't putting in any VALUES. — StackSlave
– StackSlave, Commented Mar 4, 2021 at 23:22
Why would you want to replace the perfectly fine, sleek, set based and performant INSERT ... SELECT ... with a slow and pesky procedural solution? — sticky bit
– sticky bit, Commented Mar 4, 2021 at 23:30
On second thought: What's the table grade_point supposed to model anyway? The relation between numerical and letter representation of grades? In that case you don't want to fill it according to the rows of takes but just one time, one row for every possible grade (which must not necessarily currently exists in takes). (And afterwards change takesso that grade is a foreign key pointing to the records in grade_point.) — sticky bit
– sticky bit, Commented Mar 4, 2021 at 23:38
And yes, creating a procedure won't insert or update or delete any rows. You need to execute the procedure so that any INSERT (or UPDATE or DELETE) statements in its body have any effect. (Should that have been the question you have. It's all a little hard to decipher what you have and want and what the problem is. At least for me...) — sticky bit
– sticky bit, Commented Mar 4, 2021 at 23:41
@stickybit I was trying to visualize the books problem sir. The question was not related with the real-world problem. I'm currently working for a troubled-exam. Thanks for sharing your ideas with me. Regards — Ahmet
– Ahmet, Commented Mar 5, 2021 at 8:53

nbk · Accepted Answer · 2021-03-05 00:45:20Z

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

answered Mar 5, 2021 at 0:45

nbk

49.6k8 gold badges32 silver badges53 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

Ahmet Over a year ago

"I don't know, why you want to do this, when a simpe insert is enough", I'm studying to the qualification exam. Exam community can ask anything. Anyway, thanks. Though I didn't understand why "db<>fiddle" threw an error to my syntax. The query works fine on MySql Workbench.

nbk Over a year ago

dbfiddle, has some querks, that have to be considered see dbfiddle.uk/… , besides try the next time DELIMITER $$ and add the semicolins, it make for ebtter rad and MySQL while accepting your style get confused in dbfiddle

Ahmet Over a year ago

I wonder what does "..while accepting your style get confused.." means?

nbk Over a year ago

you have a delimiter without an actual character, this does through dbfiddle in its configuration off. dbfiddle.uk doesn't like the DELIMITER because it s adds it automaticallly as you can see in the fiddles. like i said, it takes some time to kn owall rules of the fiddle sites

Ahmet Over a year ago

Ok, thanks for the information, I will be careful next time. Regards

Collectives™ on Stack Overflow

MySql - Inserting into the table in a while loop

1 Answer 1

5 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

5 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related