CODEWITHSUNDEEP
mysqlsql
Jada Developer
Jada Developer

Reputation: 126

Continuously SQL to a Point

I want to make a continuously SQL until reach max ID.

INSERT INTO creature_classlevelstats (`level`, `class`) 
VALUES 
(101, 1),
(101, 2),
(101, 4),
(101, 8),
(102, 1),
(102, 2),
(102, 4),
(102, 8),
(103, 1),
(103, 2),
(103, 4),
(103, 8),
(104, 1),
(104, 2),
(104, 4),
(104, 8);

This is example of what i'll be doing, sadly that this will be long since the max can be 255. I have Tried this : 

DECLARE @lvl INT
SET @lvl = 101
WHILE (@lvl <= 255)
BEGIN
INSERT INTO creature_classlevelstats (`level`, `class`) values 
(101, 1),
(101, 2),
(101, 4),
(101, 8);
SELECT @lvl = @lvl + 1
END

This is the Error

[Err] 1064 - You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'DECLARE @lvl INT

Upvotes: 0

Views: 74

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1270793

You can use a recursive CTE:

with recursive cte as (
      select 1 as lev
      union all
      select lev + 1
      from cte
      where lev < 255
     )
insert into creature_classlevelstats (level, class) 
    select 100 + cte.lev, d.class  
    from (select 1 as class union al
          select 2 union all
          select 4 union all
          select 8
         ) d cross join
         cte;

Upvotes: 0

Cyril Rebreyend
Cyril Rebreyend

Reputation: 310

I think you want to do something like this (WHILE has to be in a proc or function, and you don't need to DECLARE):

DELIMITER $$

CREATE PROCEDURE test()
    READS SQL DATA
BEGIN

    SET @lvl = 101;
    WHILE (@lvl <= 255) DO
     INSERT INTO creature_classlevelstats (`level`, `class`) values 
        (@lvl, 1),
        (@lvl, 2),
        (@lvl, 4),
        (@lvl, 8);
        SET @lvl = @lvl + 1;
    END WHILE;

END $$


DELIMITER ;

CALL test()

Upvotes: 1

Related Questions