title: How do I rewrite my MySQL update statement to eliminate "Truncated incorrect INTEGER value" warnings?
body: <p>I'm using MySQL 5.5.37.  I want to eliminate the warnings from my update statement, which are shown below ...</p>
  
  <pre><code>update resource r 
      set grade_id = convert(substring_index(substring_index(
                     r.description, 'Grade ', -1), ' ', 1), unsigned integer) 
      where r.description like '% Grade%' 
        and CONVERT(SUBSTRING_INDEX(SUBSTRING_INDEX(
                     r.description, 'Grade ', -1), ' ' ,1),UNSIGNED) &gt; 0;
  
  Query OK, 0 rows affected, 7 warnings (0.02 sec)
  Rows matched: 1333  Changed: 0  Warnings: 7
  
  mysql&gt; show warnings;
  +---------+------+--------------------------------------------------+
  | Level   | Code | Message                                          |
  +---------+------+--------------------------------------------------+
  | Warning | 1292 | Truncated incorrect INTEGER value: ''            |
  | Warning | 1292 | Truncated incorrect INTEGER value: ''            |
  | Warning | 1292 | Truncated incorrect INTEGER value: ''            |
  | Warning | 1292 | Truncated incorrect INTEGER value: 'MyCo'        |
  | Warning | 1292 | Truncated incorrect INTEGER value: 'MyCo'        |
  | Warning | 1292 | Truncated incorrect INTEGER value: 'MyCo'        |
  | Warning | 1292 | Truncated incorrect INTEGER value: 'MyCo'        |
  +---------+------+--------------------------------------------------+
  </code></pre>
  
  <p>What I do not understand is how I can rewrite my query differently given that none of the values being updated match what the warnings are complaining about.  BElow is my query where I list the distinct values that are being updated ...</p>
  
  <pre><code>mysql&gt; select distinct substring_index(substring_index(
                r.description, 'Grade ', -1), ' ', 1)
            from resource r
            where r.description like '% Grade%'
              and CONVERT(SUBSTRING_INDEX(SUBSTRING_INDEX(
                r.description, 'Grade ',-1),' ',1),UNSIGNED) &gt; 0;
  +-----------------------------------------------------------------------+
  | substring_index(substring_index(r.description, 'Grade ', -1), ' ', 1) |
  +-----------------------------------------------------------------------+
  | 7                                                                     |
  | 8                                                                     |
  | 11                                                                    |
  | 9                                                                     |
  | 12                                                                    |
  | 10                                                                    |
  | 6                                                                     |
  +-----------------------------------------------------------------------+
  </code></pre>
  
  <p>How do I rewrite my update statement so that it updates the same values without tryihng to truncate incorrect integers?</p>