title: How can I delete MySQL records which have a field which is not numeric?
body: <p>Due to a data corruption, we have a database field (Mysql 5) which has been populated with text. Its a custom field in the ExpressionEngine CMS and was not set as numeric only. I need to delete only those records for which this field has been incorrectly populated. It contains a URL instead of an ID e.g. 10937.</p>
  
  <p>I need to run a query / stored procedure which will find only those records which have been incorrectly populated i.e do not contain a numeric ID and delete those records, plus linked records in another table.</p>
  
  <p>I would welcome advice on the best way to do this. I could do it from PHP but was hoping to do it in a stored procedure as this would be a useful skill to learn. <a href="https://stackoverflow.com/questions/2756736/how-to-only-select-numeric-data-from-mysql">This question</a> has details of a function which can test if a field is numeric. But I actually need the opposite.</p>
  
  <p>The query I have which will select the records in question is:</p>
  
  <pre><code>SELECT field_id_58 as 'release_id' , ewt.entry_id FROM exp_weblog_data ewd
  LEFT JOIN exp_weblog_titles ewt ON ewt.`entry_id` = ewd.`entry_id`
  WHERE ewt.weblog_id = 15
  </code></pre>
  
  <p>This returns all the cms entry_ids I am dealing with, including the ones I want to delete. The field with the incorrect data is field_id_58. The fact that this field has been incorrectly populated means we now have duplicate records in tables ewt and ewd.</p>
  
  <p>Here's what I have so far:</p>
  
  <pre><code>DELIMITER //
  
  CREATE PROCEDURE `proc_DEL_DUPLICATE_PR`
  BEGIN
      DECLARE empty INT;
      DECLARE result ??? # not sure what data type this should be
      DECLARE cur1 CURSOR FOR SELECT field_id_58 as 'release_id' , ewt.entry_id FROM exp_weblog_data ewd
                              LEFT OUTER JOIN exp_weblog_titles ewt ON ewt.`entry_id` = ewd.`entry_id`
                              WHERE ewt.weblog_id = 15;
      DECLARE CONTINUE HANDLER FOR NOT FOUND SET empty=1;
      OPEN cur1;
      SET empty =0;
      WHILE empty = 0 DO
          FETCH cur1 INTO result;
          #if the release_id in the result row contains a URL string then delete rows for tables ewt, ewd for that entry_id
          END IF;
      END WHILE;
  
      CLOSE cur1;
  
  END //
  </code></pre>