WHILE is not valid input at this position when running SQL in MySQL workbench

Question

I have created a table as follows:

create table data_table(skey int, svalue int);

I want to find gaps in skey. Say I have entries 1,2,6,7. Then running SQL should return 4 and 5. I referred this and tried executing following:

select @min_val := min(svalue), @max_val := max(svalue) from data_table;

create table tmp (Field_No int);

WHILE @min <= @max DO
   if not exists (select * from data_table where skey = @min)
      insert into tmp (Field_No) values (@min)
   set @min = @min + 1
END WHILE;

select * from tmp
drop table tmp

MySQL workbench says WHILE is not valid input at this position:

PS: Am using MySQL 5.6.25

Update

Adding the whole code in stored procedure still gives me errors:

PLSQL is language used by Oracle Database, not MySQL. I think you have mixed syntaxes here. — Madhur Bhaiya, Sep 23 '19 at 10:41
ok removed "PL" as I indeed referred to [MySQL documentation for `WHILE`](https://dev.mysql.com/doc/refman/5.7/en/while.html). — MsA, Sep 23 '19 at 11:19
'MySQL supports the IF, CASE, ITERATE, LEAVE LOOP, WHILE, and REPEAT constructs for flow control within stored programs' - https://dev.mysql.com/doc/refman/8.0/en/flow-control-statements.html Your code does not appear to be in a stored program. — P.Salmon, Sep 23 '19 at 11:33
Added the whole code inside stored procedure. Still getting errors. SQL noob here. Seems that I miss a lot of SQL. Adding semicolons everywhere sill giving me a lot of errors. — MsA, Sep 23 '19 at 12:38
Please add code as text which we can use rather than images which we cannot.AND I don't see the use of delimiters. — P.Salmon, Sep 23 '19 at 12:50

score 0 · Answer 1 · answered Sep 23 '19 at 12:55

Every if must have a then and and end if; If you have more than 1 statement in the procedure you must set delimiters.https://dev.mysql.com/doc/refman/8.0/en/stored-programs-defining.html

This syntaxs

drop procedure if exists p;
delimiter $$
create procedure p()
begin 
    select @min_val := min(svalue), @max_val := max(svalue) from data_table;

    create table tmp (Field_No int);

    WHILE @min <= @max DO
    if not exists (select * from data_table where skey = @min) then
        insert into tmp (Field_No) values (@min);
        set @min = @min + 1;
    end if;
    END WHILE;

    select * from tmp;
    drop table tmp;
end $$
delimiter ;

But your logic is off you seem to confusing @min_val and @min

WHILE is not valid input at this position when running SQL in MySQL workbench

1 Answers1