Continuously SQL to a Point

Question

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

imho you miss to create a procedure to wrap the code into if you are using mysql — Lelio Faieta, Jan 22 '20 at 09:23
Seems like it is tho the reason is i don't understand the procedure or how i'll be finding the code on later.. — Jada Developer, Jan 22 '20 at 10:23
Regarding the error please read https://stackoverflow.com/questions/11754781/how-to-declare-a-variable-in-mysql — P.Salmon, Jan 22 '20 at 11:21

score 1 · Accepted Answer · edited Jan 22 '20 at 14:56

1

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()

edited Jan 22 '20 at 14:56

Jada Developer

126
8

answered Jan 22 '20 at 09:36

Cyril Rebreyend

310
2
6

Excuse my bad but where could i find the PROCEDURE test i means i see the SHOW PROCEDURE STATUS; but sadly don't know how to find the result. – Jada Developer Jan 22 '20 at 09:57
@JadaDeveloper the result of this procedure will be that the rows are inserted in your table. How is "show procedure status" related to this code? – Lelio Faieta Jan 22 '20 at 10:27
I think you mean "how to use this procedure ?" in this case : - Create the procedure with the above statement (it will be called "test" unless you change it). Then open a new window and write "CALL test". This will call the procedure to be executed. – Cyril Rebreyend Jan 22 '20 at 11:23
@LelioFaieta Nothing was inserted to the table. no errors tho. – Jada Developer Jan 22 '20 at 11:52
@CyrilRebreyend When i did that it inserted only 101 and gives me duplication error. – Jada Developer Jan 22 '20 at 11:53
@JadaDeveloper that's probably because you have defined as unique the value in the level and you are trying to insert multiple times the 101 value. The procedure works but the second insert is dropping an error for duplicate key on level field – Lelio Faieta Jan 22 '20 at 11:58
Modified the code to make it work (changed SELECT to SET). Sorry. – Cyril Rebreyend Jan 22 '20 at 12:11
@CyrilRebreyend Exactly that was the issue, thank you! – Jada Developer Jan 22 '20 at 14:31

Gordon Linoff · Answer 2 · 2020-01-22T16:36:19.237

0

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;

edited Jan 22 '20 at 16:36

answered Jan 22 '20 at 11:44

Gordon Linoff

1,242,037
58
646
786

This doesn't work sadly, insert into creature_classlevelstats (`level`, `class`) select d.`level` – Jada Developer Jan 22 '20 at 11:56
Looking at the comments on the other answer that's because of the duplicate entry constrain you probably have on level field – Lelio Faieta Jan 22 '20 at 11:59
1

@JadaDeveloper . . . Include the columns that *you* want in the final table. This answer is based on YOUR question. – Gordon Linoff Jan 22 '20 at 12:47
@LelioFaieta Well it wasn't unique level means level can be 101 - class 1 and 101 - class 2 and 101 class 4 etc.... without any issues of duplication the dup i mentioned was about the PROCEDURE test it was duplicated. – Jada Developer Jan 22 '20 at 14:34
@JadaDeveloper . . . I was looking at your `while` loop. In any case, what you want is even simpler than what I started with. – Gordon Linoff Jan 22 '20 at 16:36

Continuously SQL to a Point

2 Answers2