How to rewrite my MySQL update statement to eliminate the warnings "Truncated Incorrect INTEGER Values"?
I am using MySQL 5.5.37. I want to exclude warnings from my update instruction, 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' |
+---------+------+--------------------------------------------------+
I donโt understand how I can rewrite my request differently, considering that none of the updated values โโmatches what the warnings warn about. BElow is my query where I list various values โโthat are 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 the wrong integers?
, - WHERE
CONVERT(SUBSTRING_INDEX(SUBSTRING_INDEX(r.description, 'Grade ',-1),' ',1),UNSIGNED) > 0
, - INTEGER ''
MyCo
.
| 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'
, description
resource
.
description
Foo Grade 100 Bar
Foo Grade 99 Grade
Foo Grade 98 Grade MyCO
, Grade
.
SUBSTRING_INDEX(SUBSTRING_INDEX(r.description, 'Grade ',-1),' ',1),
.
, , , .
, column.If , UNION
. .
.
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]+';
, .
- WHERE, SELECT. ( ) 0
CONVERT(SUBSTRING_INDEX(SUBSTRING_INDEX(
r.description, 'Grade ',-1),' ',1),UNSIGNED) > 0
retuns false
. , . select, , :
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%';
: http://rextester.com/TVHN10678
, .
trim() , , ? .
, :
r.description '% Grade%' SUBSTRING_INDEX (SUBSTRING_INDEX ( r.description, 'Grade', -1), '', 1) REGEXP '^ [0-9] + $';
, : CONVERT - :
r.description '% Grade%' SUBSTRING_INDEX (SUBSTRING_INDEX ( r.description, 'Grade', -1), '', 1) > 0;
(- , , - " " ).
- resource.description
''
, MyCo
. , convert
, .
, , , , resource.description
.
select distinct substring_index(substring_index(r.description, 'Grade ', -1), ' ', 1)
from resource r
order by r.description
EXPLAIN
. . MySQL, , , - , where
.