Mysql Procedure error while executing it

MySQLmysql-5.5mysql-5.6stored-procedures

I am creating procedure which selects member_id in courser.member_id having more that 100000 records. i had declared one variable i which stores one member id at time update it with next query.

can anyone resolve this..? procedure is as follows:-

    DELIMITER //
DROP PROCEDURE IF EXISTS Mock_test_1;
CREATE PROCEDURE Mock_test_1()
   BEGIN
DECLARE i int unsigned;
DECLARE curs1 CURSOR FOR select member_id from client_master;
open curs1;
read_loop: LOOP
      FETCH curs1 INTO i;
update client_master set
         `mobileno`='0000001231', 
         `email`=concat('MOCK',member_id,'@mock.com'), `password`=md5('MOCK'),
         `personaladdress` = concat('MOCK',member_id),
         businessaddress = concat('MOCK',member_id),
         imeino = '00000000000000', panno= 'AAAAA66666' ,aadharno = '11111111111111';
insert into client_master_test select * from client_master;
   END //
 DELIMITER ;

can anyone suggest me blocker for this..?

Best Answer

  • You are fetching into i, but not using it.
  • There is no end loop.
  • The insert is copying the whole table over repeatedly?

Consider using a single multi-table UPDATE, not a Stored Procedure with a loop.

Related Question