Update MySQL field; condition = string field between two numbers <p>I am trying to update a field's value if a string column is between two numbers.</p> <pre><code>UPDATE SAMPLE.EXAMPLE SET modNum = CONCAT(modNum,"26") WHERE modNum NOT LIKE '%26%' AND (procKey BETWEEN 90000 AND 99123 OR procKey = 77444); </code></pre> <p>Unfortunately I get an error: </p> <blockquote> <p>Error Code: 1292. Truncated incorrect DOUBLE value: '4123F '</p> </blockquote> <p>I am surprised because when I do a select I get the expected results.</p> <pre><code>SELECT * FROM SAMPLE.EXAMPLE WHERE modNum NOT LIKE '%26%' AND (procKey BETWEEN 90000 AND 99123 OR procKey = 77444); </code></pre> <p>Create table statement:</p> <pre><code>'CREATE TABLE `example` ( `id` int(11) NOT NULL AUTO_INCREMENT, `modNum` char(4) DEFAULT NULL, `procKey` char(8) DEFAULT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=latin1' </code></pre> <p>Sample Data:</p> <pre><code>INSERT INTO `SAMPLE`.`EXAMPLE` (`modNum`, `procKey`) VALUES ('42', '99001'); INSERT INTO `SAMPLE`.`EXAMPLE` (`modNum`, `procKey`) VALUES ('42', '9900f'); </code></pre> <p>What can I do to update the columns given my conditions?</p> <hr> <hr> <p>To me it looks like a bug. The best I can come up with is to check if the value of procKey is an integer by using regular expressions. I changed my UPDATE statement to be:</p> <pre><code>UPDATE SAMPLE.EXAMPLE SET modNum = CONCAT(modNum,"26") WHERE modNum NOT LIKE '%26%' AND procKey REGEXP '^[0-9]+$' AND (procKey BETWEEN 90000 AND 99123 OR procKey = 77444); </code></pre> <p>Please, let me know if there is a better way.</p>