Question 21018 in Mysql Question posted in
The official MySQL documentation can be found here.

MySQL Workbench – I want to loop through 100 columns which are named `1`-`100`

I am using MySQL Workbench. I am trying to build a table with random dummy data that I am generating using a loop. I want to loop through 100 columns which are named 1-100. How can this be accomplished?

Here is what I have so far. I get Error Code: 1054 Unknown column ‘count’ in ‘field list’.

DELIMITER $$
DROP PROCEDURE IF EXISTS insertUsingLoop3$$
CREATE PROCEDURE insertUsingLoop3()
BEGIN
   DECLARE count INT DEFAULT 0;
   DECLARE randValue INT DEFAULT 33;
   
   WHILE count < 101 DO
        SET randValue = FLOOR( RAND() * (127-33) + 33);
        INSERT INTO test1(count)
            VALUES(CHAR(randValue));
      SET count = count + 1;
   END WHILE;
END$$
DELIMITER ;

I was hoping to use the variable – ‘count’ as the column name since I named 100 columns using the numbers 1 – 100.

Tags:

2

Answers


  1. Chosen as BEST ANSWER
    0

    Using PHP with WAMP I was able to figure out a solution to what I was trying to do. Here is what I came up with.

        function insColmns() {
  // loop through columns named '1', '2', ... '98', '99', '100'
  $counter1 = 1;
  $colmns = '';

  while($counter1 <= 100) {
      $strCounter1 = (string) $counter1;
      if($counter1 < 100) {
        $colmns .= 'c' . $strCounter1 . ', ';
      } else {
        $colmns .= 'c' . $strCounter1;
      }
      
      $counter1++;
  }

  return $colmns;
}

function insData() {
  // loop to insert random character into each of the columns
  $counter2 = 1;
  $data = '';

  while($counter2 <= 100) {
    $randValue = rand(384,591);
    $unicodeChar1 = (string) mb_chr($randValue);

    if($counter2 < 100) {
      $data .= '"' . $unicodeChar1 . '", '; 
    } else {
      $data .= '"' . $unicodeChar1 . '"';
    }

    $counter2++;
  }

  return $data;
}


$sql = 'INSERT INTO testing.sample2 (' . insColmns() .
')
VALUES (' . insData() . ')';

  2. 0

    You can create a query as a string and then execute it.
    In your case, the query may be as follows:

    WHILE count < 101 DO
   SET randValue = FLOOR( RAND() * (127-33) + 33);
   SET @q = CONCAT('INSERT INTO test1(c', count,') VALUES(CHAR(',randValue, '))');
   PREPARE stmt FROM @q;
   EXECUTE stmt;
   DEALLOCATE PREPARE stmt;
   SET count = count + 1;
END WHILE;
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search