MySQL query Error with IF, END IF

Question

I have a problem in MySQL query with IF, END IF

I made a code like this:

SET @columnCnt = 1;
SET @indexCnt = 1;
SET @dataCnt = (SELECT COUNT(*) FROM `test`);
WHILE @indexCnt < @dataCnt DO
    IF @indexCnt != @columnCnt THEN
            IF (SELECT COUNT(*) FROM `test` WHERE `idx` = @columnCnt) > 0 THEN
                    UPDATE `test` SET `idx` = @indexCnt WHERE `idx` = @columnCnt;
                    @indexCnt = @indexCnt + 1;
            END IF;
    END IF;

    @columnCnt = @columnCnt+1;
END WHILE;

And I get Error like this.

MySQL Message :
#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'WHILE @indexCnt < @dataCnt DO
    IF @indexCnt != @columnCnt THEN
            IF ' at line 1

How can I solve this problem?

Where are you executing this? Does this occur inside a function or stored procedure? — Michael Berkowski, Oct 23 '13 at 14:20
MySQL's flow control statements (if/while) can't be used in a plain client query context - you have to use them in a function or stored procedure. — Michael Berkowski, Oct 23 '13 at 14:21
See also http://stackoverflow.com/questions/12954095/mysql-declare-while-outside-stored-procedure-how... — Michael Berkowski, Oct 23 '13 at 14:22

score 0 · Accepted Answer · answered Oct 23 '13 at 15:26

You can try something like this;

SET @columnCnt = 1
SET @indexCnt = 1
SET @dataCnt = (SELECT COUNT(*) FROM `test`)
WHILE (@indexCnt < @dataCnt)
Begin
    IF @indexCnt <> @columnCnt 
    Begin
            IF (SELECT COUNT(*) FROM `test` WHERE `idx` = @columnCnt) > 0 
            Begin
                    UPDATE `test` SET `idx` = @indexCnt WHERE `idx` = @columnCnt
                    SET @indexCnt = @indexCnt + 1;
            End
    End

   SET @columnCnt = @columnCnt+1
End

MySQL query Error with IF, END IF

1 Answers1