How to use nested loops and variables in MySQL statement

Question

I'm trying to write a sql statement that will insert data given a few setup variables. I don't want to create a stored procedure, as it's not something I'll be dealing with anywhere except as an administrator and I don't want the stored procedure hanging around. I just want a statement that works. Here's what I have so far:

SET @app = 7;
SET @time = UNIX_TIMESTAMP(NOW());
SET @maxValue = 100;
SET @decrement = 10;
SET @category = 1;
SET @minSubcategory = 0;
SET @maxSubcategory = 19;
SET @subcategory = @minSubcategory;
subcat_loop: LOOP
  SET @idx = 0;
  insert_loop: LOOP
    SET @id = CONCAT('TempId', @idx+1);
    SELECT @name:=username FROM user WHERE id = @id;
    SET @value = @maxValue - @decrement * @idx;
    INSERT INTO data (userId, username, app, category, subcategory, value, date) VALUES
      (@id,  @name, @app, @category, @subcategory, @value, @time);
    SET @idx = @idx+ 1;
    IF @idx > 10 THEN
      LEAVE insert_loop;
    END IF;
  END LOOP insert_loop;
  SET @subcategory = @subcategory + 1;
  IF @subcategory > @maxSubcategory THEN
    LEAVE subcat_loop;
  END IF;
END LOOP subcat_loop;

But it doesn't like the SET @idx = 0 inside the loop for some reason. What am I doing wrong?

Note that this is probably the first time I've tried doing anything this complicated with MySQL, and my little knowledge is probably more dangerous than being completely oblivious, so let me know if I'm going about this the wrong way completely (although I really, really don't want a stored procedure for this).

Ike Walker · Accepted Answer · 2010-11-05 16:35:50Z

3

Unfortunately you can't use LOOP outside of a stored program: stored procedures, stored functions, and triggers.

You do have some options:

You can create a stored procedure and restrict the privileges so other users can't execute it.
Another option is to create a stored procedure temporarily in your script, run it, then drop it at the end of the script.

Otherwise, if you still don't want to create a stored procedure, your best bet is to write a small script in shell, python, etc to do your looping.

Good luck!

answered Nov 5, 2010 at 16:35

Ike Walker

65.8k14 gold badges115 silver badges112 bronze badges

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

1 Comment

Ed Marty Over a year ago

Thanks, I was afraid of that.

Collectives™ on Stack Overflow

How to use nested loops and variables in MySQL statement

1 Answer 1

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related