problem in loop mysql procedure

Question

I want to replicate my data into the database for that i have written below script,,

BEGIN
DECLARE a INT Default 1;

DECLARE counter INT;

SET counter=12;

WHILE counter<1010 DO
IF a=1 THEN
    insert into problem
    values(counter,'d0008','blood pressure','ajay','25-jun-1990');

    insert into med
    values(counter,'d0008','beta blocker','ajay','25-jun-1990');

    SET a=2;
END IF;

IF a=2 THEN    
    insert into problem
    values(counter,'d0009','headache','amit','25-jan-1990');

    insert into med
    values(counter,'d0009','aspirin','amit','25-jan-1990');

    SET a=3;    
END IF;

IF a=3 THEN    
    insert into problem
    values(counter,'d0011','tension','anil','25-feb-1990');

    insert into med
    values(counter,'d0011','capsule','anil','25-feb-1990');

    SET a=4;    
END IF;

IF a=4 THEN    
    insert into problem
    values(counter,'d0012','pain','ved','25-mar-1990');

    insert into med
    values(counter,'d0012','dcold','ved','25-mar-1990');

    SET a=1;    
END IF;

SET counter=counter+1;

END WHILE;
END

Note: first field of both the table is primary key...

I am not able to iterate this 1000 time through loop and I am getting below error

call abc();
ERROR 1062 (23000): Duplicate entry '12' for key 'PRIMARY'

I dont know what is the problem into this procedure..

dee-see · Accepted Answer · 2011-05-09 11:52:10Z

1

The first if is executed with counter == 12 and a == 1. Then you set a = 2, which has for effect that the second if runs and counter is still 12 so the primary key error is thrown. You're inserting two rows with the same primary key.

What you need, if I understood correctly, is to use an IF-ELSEIF structure, and not 4 differents IFs.

edited May 9, 2011 at 11:52

answered May 9, 2011 at 11:46

dee-see

24.1k6 gold badges63 silver badges92 bronze badges

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

Collectives™ on Stack Overflow

problem in loop mysql procedure

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related