title: Update MySQL field; condition = string field between two numbers
body: <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>