[FIXED] How do I rewrite my MySQL update statement to eliminate "Truncated incorrect INTEGER value" warnings?

Issue

I'm using MySQL 5.5.37. I want to eliminate the warnings from my update statement, which are shown below ...

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) > 0;

Query OK, 0 rows affected, 7 warnings (0.02 sec)
Rows matched: 1333  Changed: 0  Warnings: 7

mysql> 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'        |
+---------+------+--------------------------------------------------+

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 ...

mysql> 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) > 0;
+-----------------------------------------------------------------------+
| substring_index(substring_index(r.description, 'Grade ', -1), ' ', 1) |
+-----------------------------------------------------------------------+
| 7                                                                     |
| 8                                                                     |
| 11                                                                    |
| 9                                                                     |
| 12                                                                    |
| 10                                                                    |
| 6                                                                     |
+-----------------------------------------------------------------------+

How do I rewrite my update statement so that it updates the same values without tryihng to truncate incorrect integers?

Solution

As far as my knowledge concern you get the warning due to WHERE clause condition

CONVERT(SUBSTRING_INDEX(SUBSTRING_INDEX(r.description, 'Grade ',-1),' ',1),UNSIGNED) > 0

As clearly mention in warning statement Truncated due to incorrect INTEGER value for '' and MyCo value.

| 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' 

Which indicate that you did not have any specific pattern for value in description column of resource table.

Like below

description
Foo Grade 100 Bar
Foo Grade 99 Grade 
Foo Grade 98 Grade MyCO

As shown in above code in second & third row you have multiple Grade string in record. Which indirectly affect SUBSTRING_INDEX(SUBSTRING_INDEX(r.description, 'Grade ',-1),' ',1), line. So what my suggestion is please make sure that all record inserted properly. If all record are inserted properly and Check that they contains multiple types of pattern for descripton column.If contains multiple pattern then you have to rewrite this query using UNION. Single query for one pattern and another query for another pattern.

And just try below updated query.

   update resource r 
    set grade_id = convert(substring_index(substring_index(
                   r.description, 'Grade ', -1), ' ', 1), unsigned integer) 
    where r.description like '% Grade%' 
    and substring_index(substring_index(r.description, 'Grade ', -1), ' ', 1) REGEXP '[0-9]+';

Hope this explanation helps you.

Answered By - Sagar Gangwal

Answer Checked By - Cary Denson (PHPFixing Admin)