CODEWITHSUNDEEP
mysqlsqlstored-proceduresmariadbsql-insert
ali
ali

Reputation: 117

insert rows with a loop in mysql query

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

Upvotes: 2

Views: 1346

Answers (1)

GMB
GMB

Reputation: 222442

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 ;

Upvotes: 2

Related Questions