How to update numbers in a column given a condition in MySQL?

Question

I am updating a table in MySQL with a column of cell phones, where I find badly loaded cell phones. These have less number of numbers than they should. These I need to replace with "0".

I tried it in MySQL version 8.0.3 These were my attempts with code:

--first try

UPDATE usuarios_registrados
    SET celular = '0'
    WHERE celular <= 1
    OR celular >= 19999999;

--second try

UPDATE usuarios_registrados
    SET celular = 0
    WHERE celular
    BETWEEN 1
    AND 19999999;

These were the errors:

--first try

Error Code: 1292. Truncated incorrect DOUBLE value: '02392-15635834'    0.204 sec

--second try

Error Code: 1292. Truncated incorrect DOUBLE value: '02392-15635834'    0.125 sec

Answer 1

It seems celular is a string type column, and you're trying to update by using numeric values, and problem occurs due to this. Against this problem, creating a cursor within a procedure, and calling it might be a robust way :

CREATE PROCEDURE Upd_Celular()
BEGIN
  DECLARE v_celular   varchar(50);
  DECLARE v_celular2  int;
  DECLARE v_celular3  int;
  DECLARE v_id        int;
  DECLARE finished BOOL DEFAULT FALSE;

  DECLARE cur CURSOR FOR 
  SELECT id, celular, 
         case when strcmp( cast(celular as signed) , celular ) = 0 then 1 else 0 end 
         as celular2, cast(celular as signed) as celular3
    FROM usuarios_registrados;

  DECLARE CONTINUE HANDLER FOR NOT FOUND SET finished = TRUE;

  OPEN cur;

  REPEAT FETCH cur INTO v_id,v_celular,v_celular2,v_celular3;
  IF  NOT finished 
    AND v_celular2 != 0  
    AND v_celular3 BETWEEN 1 AND 19999999
  THEN
    UPDATE usuarios_registrados s
       SET s.celular = '0'
     WHERE s.id = v_id;   
  END IF;

  UNTIL finished END REPEAT;

  CLOSE cur;
END;

Demo