title: MySql - Inserting into the table in a while loop
body: <p>I have a <code>takes</code> table, and i want to convert each grade record to the numeric value and store it in the <code>grade_point</code> table .</p>
  <p><a href="https://i.stack.imgur.com/3VmoQ.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/3VmoQ.png" alt="enter image description here" /></a></p>
  <p>I initialized the <code>grade_point</code> as:</p>
  <pre class="lang-sql prettyprint-override"><code>CREATE TABLE IF NOT EXISTS university.grade_point (
      id VARCHAR(5),
      grade VARCHAR(2),
      grade_value NUMERIC(2 , 1)
  );
  </code></pre>
  <p>I can approach the problem as in the following:</p>
  <ul>
  <li><p>Get <code>id</code> from <code>takes</code> table</p>
  </li>
  <li><p>Get <code>grade</code> from <code>takes</code> table</p>
  </li>
  <li><p>If <code>grade</code> is <code>A</code> then <code>grade_value</code> is 4.0</p>
  <p>If <code>grade</code> is <code>A-</code> then <code>grade_value</code> is 3.7 ...</p>
  </li>
  </ul>
  <p>I can write the following query:</p>
  <pre class="lang-sql prettyprint-override"><code>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;
  </code></pre>
  <p>I wonder if I can do that using <code>procedure</code>?</p>
  <p>Inspired from the accepted solution of <a href="https://stackoverflow.com/questions/5817395/how-can-i-loop-through-all-rows-of-a-table-mysql">How can I loop through all rows of a table (MySQL)</a>:</p>
  <ul>
  <li><p>For each <code>grade</code> record in the <code>takes</code> table</p>
  <ul>
  <li><p>Select the <code>i</code>th row</p>
  </li>
  <li><p>Get <code>id</code>, <code>grade</code> and convert <code>grade</code> to <code>grade_value</code></p>
  </li>
  <li><p>insert it to the <code>grade_point</code></p>
  </li>
  </ul>
  </li>
  <li><p>Result is:</p>
  </li>
  </ul>
  <pre class="lang-sql prettyprint-override"><code>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 &lt; 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 ;
  </code></pre>
  <p>However, the above query responsed as:</p>
  <blockquote>
  <p>0 row(s) affected, 1 warning(s): 1050 Table 'grade_point' already exists</p>
  </blockquote>
  <p>When drop the <code>grade_point</code> table and re-execute the query i have the following response</p>
  <blockquote>
  <p>0 row(s) affected</p>
  </blockquote>
  <p>Nothing is inserted into the <code>grade_point</code> table.</p>
  <p>Could you please tell me what I'm missing or wrong in my <code>procedure</code>?</p>
  <p>Regards.</p>