Looping SQL query in MySQL Workbench

Question

trying to run a query in workbench that will cycle thru all of the site_ids and test my query against each of them. this should be easy but i'm missing something.

SET @user_id = 449;
SET @getsites.site_id = 11;
WHILE(@getsites.site_id < 535) DO
     SELECT routine goes here
     SET @getsites.site_id = @getsites.site_id + 1;
END WHILE;

Why can you not just write a simple SQL statement like select ... from table where user_id = 449 and site_id between 11 and 534? — zedfoxus
– zedfoxus, Commented Oct 15, 2015 at 15:23
will help me pin down the problem site if the query runs until it bombs, vs. just bombing right away on the whole query, which it does now. — Slopeside Creative
– Slopeside Creative, Commented Oct 15, 2015 at 15:27
You can still do that by running select ... between 11 and 272 and select ... between 273 and 534. See which one fails. If first one fails, run select ... between 11 and 142 and select ... between 143 and 272 and so on. That's like binary search and you might find the problem in fewer steps than running sequentially looking for failures. — zedfoxus
– zedfoxus, Commented Oct 15, 2015 at 15:33
If you still decide to do what what you are doing, I'd recommend stored procedure and select into — zedfoxus
– zedfoxus, Commented Oct 15, 2015 at 15:35
the query only accepts one site_id not a range so just wanted to do a simple loop or while — Slopeside Creative
– Slopeside Creative, Commented Oct 15, 2015 at 15:43

Adarsh · Accepted Answer · 2015-10-15 16:11:38Z

0

I am sharing a proc block, you can convert this with your requirements.

DELIMITER $$
USE test$$ -- database name
DROP PROCEDURE IF EXISTS proc_name$$
CREATE PROCEDURE proc_name()
BEGIN
DECLARE user_id INT(3) DEFAULT 0;
DECLARE site_id INT(4) DEFAULT 0;
SET site_id = 11;
REPEAT
    SELECT 1; -- routine goes here [execute your logic here]
    SET site_id = site_id + 1;
UNTIL site_id < 535 END REPEAT;
SELECT "Proc completed" AS Result;
END$$

DELIMITER ;

First you try with SQL, if not possible then try procedure.

answered Oct 15, 2015 at 16:11

Adarsh

7244 silver badges13 bronze badges

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

Collectives™ on Stack Overflow

Looping SQL query in MySQL Workbench

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related