title: How to use nested loops and variables in MySQL statement
body: <p>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:</p>
  
  <pre><code>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 &gt; 10 THEN
        LEAVE insert_loop;
      END IF;
    END LOOP insert_loop;
    SET @subcategory = @subcategory + 1;
    IF @subcategory &gt; @maxSubcategory THEN
      LEAVE subcat_loop;
    END IF;
  END LOOP subcat_loop;
  </code></pre>
  
  <p>But it doesn't like the <code>SET @idx = 0</code> inside the loop for some reason.  What am I doing wrong?</p>
  
  <p>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).</p>