title: Subtracting an integer from an integer in a stored procedure (SQL version 8.0.23)
body: <p>I want to decrease an integer from an integer in a stored procedure but
  I don't get the result I want.</p>
  <p>I have the following tables:</p>
  <p>Table h(</p>
  <p>i int<br />
  c int )</p>
  <p>that holds values</p>
  <div class="s-table-container">
  <table class="s-table">
  <thead>
  <tr>
  <th>i</th>
  <th>c</th>
  </tr>
  </thead>
  <tbody>
  <tr>
  <td>1</td>
  <td>100</td>
  </tr>
  </tbody>
  </table>
  </div>
  <p>Table k(</p>
  <p>j int<br />
  c int<br />
  d text<br />
  e timestamp)</p>
  <p>that holds values</p>
  <div class="s-table-container">
  <table class="s-table">
  <thead>
  <tr>
  <th>j</th>
  <th>c</th>
  <th>d</th>
  <th>e</th>
  </tr>
  </thead>
  <tbody>
  <tr>
  <td>1</td>
  <td>100</td>
  <td>NULL</td>
  <td>NULL</td>
  </tr>
  </tbody>
  </table>
  </div>
  <p>I've written the following code:</p>
  <pre><code>DROP PROCEDURE IF EXISTS a;
  
  DELIMITER //
  
  CREATE PROCEDURE a(b int, c int, d text, e timestamp)
  BEGIN
  DECLARE f INT;
  SELECT c INTO @f FROM h WHERE i = j;
  START TRANSACTION;
  CASE WHEN (@f - c) &lt; 0
  THEN SIGNAL SQLSTATE &quot;45000&quot;
  SET MESSAGE_TEXT = &quot;@f - c &lt; 0 is not allowed&quot;;
  WHEN c &lt; 0
  THEN SIGNAL SQLSTATE &quot;45000&quot;
  SET MESSAGE_TEXT = &quot;c &lt; 0 is not allowed&quot;;
  WHEN j NOT IN (select i from h)
  THEN SIGNAL SQLSTATE &quot;45000&quot;
  SET MESSAGE_TEXT = &quot;j does not exist&quot;;
  ELSE
  UPDATE h AS l SET c = (@f - c) WHERE j = i;
  INSERT INTO k (d) VALUE (d);
  INSERT INTO k (e) value (e);        
  END CASE;
  COMMIT;
  END //
  DELIMITER ;
  
  CALL a (1, 10, &quot;changed -10&quot;, &quot;2002-06-27 14:42:33&quot;);
  
  SELECT * FROM h;
  </code></pre>
  <p>Table k is updated correctly. But Table h is not.
  I want to get the following results shown for Table h after
  the code above is run.</p>
  <div class="s-table-container">
  <table class="s-table">
  <thead>
  <tr>
  <th>i</th>
  <th>c</th>
  </tr>
  </thead>
  <tbody>
  <tr>
  <td>1</td>
  <td>90</td>
  </tr>
  </tbody>
  </table>
  </div>
  <p>But instead I get</p>
  <div class="s-table-container">
  <table class="s-table">
  <thead>
  <tr>
  <th>i</th>
  <th>c</th>
  </tr>
  </thead>
  <tbody>
  <tr>
  <td>1</td>
  <td>0</td>
  </tr>
  </tbody>
  </table>
  </div>
  <p>What should be changed in my code in order to get the result I want?</p>