Mysql Script to Loop an update

Question

IS there a way to loop an update statement in mysql using mysql script. My code shown below

UPDATE table_name
SET column_name(REPLACE(column_name,Char(16), '')

I want that to loop from 16 to 31. Kindly advice.

I tried creating a script such as the one below

DELIMETER $$
CREATE DEFINER  `root`@`localhost` PROCEDURE `myupdate`()
 BEGIN
   DECLARE c INT;
   SET c  16;
     WHILE c < 31 DO
       UPDATE table_name
       SET column_name = replace(column_name, char(16), '');
       commit;
         SET c = c + 1;
     END WHILE;
  END

Kinda wrong if you ask me but I'm so new here in mysql. Kindly advice sir and Madam and Thanks in advance.

Lucas Henrique · Accepted Answer · 2014-05-22 18:26:08Z

1

You are not using = in SET c 16;. I made some changes. Try:

DELIMETER $$
CREATE DEFINER  `root`@`localhost` PROCEDURE `myupdate`()
 BEGIN
   SET @c = 16;
     WHILE @c < 31 DO
       UPDATE table_name SET column_name = replace(column_name, char(@c), '');
       SET @c = @c + 1;
     END WHILE;
  END $$
DELIMITER ;

edited May 22, 2014 at 18:26

answered May 22, 2014 at 16:47

Lucas Henrique

1,3641 gold badge11 silver badges15 bronze badges

Sign up to request clarification or add additional context in comments.

8 Comments

Roi patrick Florentino Over a year ago

How CanI use this script sir to run for all columns inside a table not just by column. Somethng like UPDATE table_name SET (all columns) = raplce((all columns),char(c), ''); In that line

Lucas Henrique Over a year ago

@Patrick A possible way is to list the columns like this:

UPDATE table_name SET column_name_1 = replace(column_name_1, char(c), ''), column_name_2 =replace(column_name_2, char(c), ''), column_name_3 =replace(column_name_3, char(c), '');

Roi patrick Florentino Over a year ago

My script does not work. :( is there a less stressing way to loop the update script?

Lucas Henrique Over a year ago

@Patrick I would in a programming language such as PHP. It did not work, but it gave some error in MySQL?

Roi patrick Florentino Over a year ago

Nah, I just need some script to run it in mysql itself. Just for studying purposes. It seems that there's more of my hair that needs pulling before I understand this one.

|

Collectives™ on Stack Overflow

Mysql Script to Loop an update

1 Answer 1

8 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

8 Comments

Your Answer

Sign up or log in

Post as a guest

Related