insert rows with a loop in mysql query

Question

I have to insert some rows for each product with a loop but i receive a syntax error. i dont know why.

This is my query:

CREATE PROCEDURE insertURL()
BEGIN

    DECLARE i INT DEFAULT 0;

    WHILE (i <= 120) DO
        INSERT INTO product_images (alt, url, `index`, product_id) 
                VALUES ('productImage', 'image/products/producte.png', 1, i);
                VALUES ('productImage', 'image/products/producte.png', 0, i);
                VALUES ('productImage', 'image/products/producte.png', 0, i);                               
                VALUES ('productImage', 'image/products/producte.png', 0, i);               
        SET i = i + 1;
    END WHILE;
END

And i receive this error:

SQL Error (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 " at line 4

Procedure looks fine but you don't appear to be setting delimiters, — P.Salmon
– P.Salmon, Commented Apr 20, 2020 at 10:37
Multi-row INSERT syntax: `INSERT INTO t (...) VALUES (1,2,3),(11,22,33), ... ,(66,55,44); — Rick James
– Rick James, Commented May 2, 2020 at 2:16

GMB · Accepted Answer · 2020-04-20 10:05:58Z

2

If you are running MySQL 8.0 (or MariaDB 10.2 or higher), you can use a recursive query for this. This should be much more efficient that using a loop.

insert into product_images (alt, url, `index`, product_id) 
with recursive cte as (
    select 0 i
    union all select i + 1 from cte where i < 120
)
select 
    'productImage',
    'image/products/producte.png', 
    x.n, 
    c.i
from cte c
cross join (select 1 n union all select 0 union all select 0 union all select 0) x

The recursive common table expression generates numbers from 0 to 120. We can then cross join it with a fixed list of values (1, 0, 0, 0) to generate the rows for insert.

You can easily turn this to a procedure:

delimiter $$

create procedure inserturl()
begin
    insert into product_images (alt, url, `index`, product_id) 
    with recursive cte (
        select 0 i
        union all select i + 1 from cte where i < 120
    )
    select 
        'productImage',
        'image/products/producte.png', 
        x.n, 
        c.i
    from cte c
    cross join (select 1 n union all select 0 union all select 0 union all select 0) x;
end
$$

delimiter ;

edited Apr 20, 2020 at 10:05

answered Apr 20, 2020 at 10:00

GMB

224k25 gold badges103 silver badges151 bronze badges

Sign up to request clarification or add additional context in comments.

8 Comments

ali Over a year ago

how can i know i use which version?? i got an error again @GMB

GMB Over a year ago

@ali: select version()

ali Over a year ago

i use 10.4.6-MariaDB version but i got a new error like mine. syntax error at line 5 @GMB

ali Over a year ago

SQL Error (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 'select 0 i union all select i + 1 from cte where i < 120 ) se' at line 5 @GMB

GMB Over a year ago

I am unsure how you are running the query exactly. This works fine for me in Maria DB 10.3 and 10.4 in this db fiddle

|

Collectives™ on Stack Overflow

insert rows with a loop in mysql query

1 Answer 1

8 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

8 Comments

Your Answer

Sign up or log in

Post as a guest

Related