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

Question

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?

I already did. Its in the last query "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;". If you want me to run another query, tell me what you want me to run. — Dave, Mar 09 '17 at 20:57
Could you possibly show what the _raw_ data is and what you want the _final_ result in the table (after update) to look like? — Dhruv Saxena, Mar 12 '17 at 19:28
Maybe but help me out first. How will knowing the raw data solve the "Truncated incorrect INTEGER value: ''" error? Isn't that error based on the value being returned from "convert(substring_index(substring_index(r.description, 'Grade ', -1), ' ', 1), unsigned integer)"? — Dave, Mar 12 '17 at 22:00
Please show us a line with `MyCo` in it. From that, we can work _forward_. — Rick James, Mar 13 '17 at 04:46
How do I get that? The warnings are in direct response to the UPDATE statement I listed above. — Dave, Mar 13 '17 at 13:43
You show a select statement that is producing the output you like. Revise the query to show the value in its original form... select distinct r.description, substring_index(substring_index(r.description, 'Grade ', -1), ' ', 1), unsigned integer)... — RMathis, Mar 16 '17 at 20:29

Sagar Gangwal · Accepted Answer · 2017-03-14T10:57:50.040

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.

Hmm.. Your example looks very similar to [this rexester demo](http://rextester.com/TVHN10678). Was that just a coincidence? — Paul Spiegel, Mar 15 '17 at 20:35

score 3 · Answer 2 · answered Mar 13 '17 at 17:34

You get the warnings because of the WHERE clause, not the SELECT clause. Since invalid (non numeric) values are converted to 0 the condition

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

retuns false. Thus these rows do not appear in the result. Remove that condition in you select statement to see which rows cause the warnings:

select distinct 
    substring_index(substring_index(
        r.description, 'Grade ', -1), ' ', 1),
    CONVERT(SUBSTRING_INDEX(SUBSTRING_INDEX(
        r.description, 'Grade ',-1),' ',1),UNSIGNED),
        r.description
from resource r
where r.description like '% Grade%';

Example: http://rextester.com/TVHN10678

Since i don't know your data, i can't help to fix it.

score 2 · Answer 3 · answered Mar 14 '17 at 11:27

2

can you use trim() before convert to remove any whitespaces that may have crept in? those are also the reason for this warning

answered Mar 14 '17 at 11:27

Ramanan

39
4

score 2 · Answer 4 · answered Mar 18 '17 at 09:35

You can try to use regular expression, something like:

where r.description like '% Grade%' and SUBSTRING_INDEX(SUBSTRING_INDEX( r.description, 'Grade ', -1), ' ' ,1) REGEXP '^[0-9]+$';

Alternatively if you are not afraid of stepping into gray area: you can also try the same query without CONVERT at all - surprisingly it may just work:

where r.description like '% Grade%' and SUBSTRING_INDEX(SUBSTRING_INDEX( r.description, 'Grade ', -1), ' ' ,1) > 0;

(again I am not sure if this is documented behavior, thus - 'gray area').

grizzthedj · Answer 5 · 2017-03-19T17:51:29.673

Somewhere in your resource.description column you have either '' or MyCo as per your warnings output. There is non-numeric data being passed to the convert function, which is where the warnings are coming from.

I don't know what your raw data looks like, but if you run the below query, it should identify any rows where resource.description is not in the correct format.

select distinct substring_index(substring_index(r.description, 'Grade ', -1), ' ', 1)
from resource r 
order by r.description

You can try running an EXPLAIN on your query as well. This may give you more insight. I don't know the internals of MySQL, but perhaps the convert function is somehow looking at all rows, despite having a where clause.

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

5 Answers5