How do I rewrite my MySQL update statement to eliminate "Truncated incorrect INTEGER value" warnings?
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.
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.
can you use trim() before convert to remove any whitespaces that may have crept in? those are also the reason for this warning