CODEWITHSUNDEEP
mysql
pavelcc
pavelcc

Reputation: 131

MYSQL - Update using while loop

declare c int 
set c = 1
while c<700 do
update users set profile_display_name = concat(substring(first_name,1,1), last_name) 
        where profile_display_name is null and id between ((c-1)*10000+1) and (c*10000);
SET c = c+1;
End while ;

I am getting error. near declare and end while statement. Where am I making mistake??

Upvotes: 3

Views: 52683

Answers (3)

pavelcc
pavelcc

Reputation: 131

This Works much better for me

DELIMITER $$

CREATE DEFINER=`ops`@`localhost` PROCEDURE `myproc`()
BEGIN
DECLARE c INT;
SET c = 1;
WHILE c < 700 DO 
SELECT CONCAT('Loop #:', c) ;

update users 
set profile_display_name = concat(substring(first_name,1,1), last_name) 
where (profile_display_name is null or profile_display_name = '')
and id between ((c-1)*10000+1) and (c*10000); 

commit;

SET c=c+1; 

END WHILE;
END

Upvotes: 7

ypercubeᵀᴹ
ypercubeᵀᴹ

Reputation: 115530

Here's how it would be defined as a stored procedure:

DELIMITER $$
CREATE PROCEDURE proc_name()
BEGIN

    DECLARE c int ;                      --- added ;
    SET c = 1 ;                          --- added ;
    WHILE c<700 DO
      UPDATE users 
        SET profile_display_name = concat(substring(first_name,1,1), last_name) 
        WHERE profile_display_name IS NULL
          AND id BETWEEN ((c-1)*10000+1) AND (c*10000);
      SET c = c + 1 ;
    END WHILE ;

END $$
DELIMITER ;

Upvotes: 3

user1191247
user1191247

Reputation: 12998

In MySQL compound statements and flow control structures can only be used in stored routines - MySQL Compound-Statement Syntax

Upvotes: 1

Related Questions