how to insert rows via loop in MySQL shell (no PHP)

Question

I would like to execute a loop in phpmyadmin which inserts rows in a table. So far I have:

DELIMITER $$

DROP PROCEDURE IF EXISTS insert_my_rows()

CREATE PROCEDURE insert_my_rows()
BEGIN
DECLARE i INT DEFAULT 376;

WHILE i<405 DO
        INSERT INTO wp_term_relationships(object_id,term_taxonomy_id,term_order) VALUES (i,16,0);
        SET i=i+1;
END WHILE;
END $$

DELIMITER ;

CALL insert_my_rows()

With this, I get an error:

#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'DELIMITER$$

DROP PROCEDURE IF EXISTS insert_my_rows()

CREATE PROCEDURE ins' at line 1

i still get #1305 - PROCEDURE sunny.insert_my_rows does not exist — brietsparks
– brietsparks, Commented Nov 22, 2014 at 6:40
@bsapaka I knew the error is not with the code you posted earlier and that is why I asked you for the entire code. Kums posted the correct answer below Change DROP PROCEDURE IF EXISTS insert_my_rows(); to DROP PROCEDURE IF EXISTS insert_my_rows; — Jaylen
– Jaylen, Commented Nov 22, 2014 at 19:17

kums · Accepted Answer · 2014-11-22 17:36:13Z

1

Syntax for the DROP PROCEDURE statement is incorrect!

Change

DROP PROCEDURE IF EXISTS insert_my_rows()

to

DROP PROCEDURE IF EXISTS insert_my_rows;

answered Nov 22, 2014 at 17:36

kums

2,7012 gold badges16 silver badges16 bronze badges

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

Comments

Ben · Accepted Answer · 2014-11-22 15:38:16Z

0

You need to end the statement with the proper delimiter. Change END to END$$.

edited Nov 22, 2014 at 15:38

Ben

10.1k5 gold badges43 silver badges42 bronze badges

answered Nov 22, 2014 at 15:05

sunysen

2,3711 gold badge15 silver badges13 bronze badges

Comments

dolmen · Accepted Answer · 2020-02-10 10:26:43Z

You must change the delimiter only while you make blocks of statements, so during the procedure definition. The DROP PROCEDURE and CALL statements needs delimiters too.

DROP PROCEDURE IF EXISTS insert_my_rows;

DELIMITER $$

CREATE PROCEDURE insert_my_rows()
BEGIN
    DECLARE i INT DEFAULT 376;

    WHILE i<405 DO
        INSERT INTO wp_term_relationships(object_id,term_taxonomy_id,term_order) VALUES (i,16,0);
        SET i=i+1;
    END WHILE;
END $$

DELIMITER ;

CALL insert_my_rows();

DROP PROCEDURE IF EXISTS insert_my_rows;

Collectives™ on Stack Overflow

how to insert rows via loop in MySQL shell (no PHP)

3 Answers 3

Comments

Comments

Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Linked

Related