I'm trying to make a list of the numbers 1-1000 for a HackerRank exercise in MySQL, version (as far as I can discover) 5.7.

Here's my code:

create table numeros 
(
    numero int not null auto_increment,
    primary key (numero)
);
loop
insert into numeros () values ();
if max(numeros.numero) = 1000 then leave; end if;
end loop;
select * from numeros

I'm getting this error:

ERROR 1064 (42000) at line 6: 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 'loop

insert into numeros () values ()' at line 1

What am I doing wrong? How can I make this loop work?

I'm trying to make a list of the numbers 1-1000 for a HackerRank exercise in MySQL, version (as far as I can discover) 5.7.

Here's my code:

create table numeros 
(
    numero int not null auto_increment,
    primary key (numero)
);
loop
insert into numeros () values ();
if max(numeros.numero) = 1000 then leave; end if;
end loop;
select * from numeros

I'm getting this error:

ERROR 1064 (42000) at line 6: 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 'loop

insert into numeros () values ()' at line 1

What am I doing wrong? How can I make this loop work?

• mysql

• Read the answer here: stackoverflow/questions/12259675/… – PM 77-1 Commented Mar 6 at 15:28
• 1 Is this code inside a stored procedure? Control flow statements (IF, LOOP, WHILE) are only allowed in stored procedures. – Barmar Commented Mar 6 at 15:37
• Ah, that'll be it. Thanks @Barmar! – user29916678 Commented Mar 6 at 15:55
• Use any system table (for example, mysql.help_relation) or 2-3 copies of such table as a rowsource and user-defined variable as iterator. – Akina Commented Mar 6 at 16:59

1 Answer 1

You can't use mySQL group function that way ...
You may use a simple counter to do this:

create table numeros 
(
    numero int not null auto_increment,
    primary key (numero)
);

CREATE PROCEDURE autoIncreant()
BEGIN
    declare c INT default 0;
    loop_name: loop
      insert into numeros () values ();
      set c=c+1;
      if c = 1000 then
        leave loop_name;
      end if;
    end loop;
END ;

call autoIncreant();

select * from numeros;

• db-fiddler Sample code

  Alternative way:

CREATE PROCEDURE autoIncreant()
BEGIN
    loop_name: loop
      insert into numeros () values ();
      select @c := MAX(numero) from numeros;
      if @c = 1000 then
        leave loop_name;
      end if;
    end loop;
END ;

db-fiddler sample code2