Mysql Loop through selected column and execute Insert query in the loop

Question

I'm trying to loop over selected slugs and execute little complicated INSERT INTO SELECT query. slugs[iteration] usage is not a correct mysql syntax. But I have to access fetched slugs one by one inside the query. How Could I achieve that ?

DELIMITER $$
CREATE PROCEDURE create_sitemap_from_slugs()
BEGIN
    SELECT `slug`  INTO slugs FROM slug_table;
    SELECT COUNT(*)  INTO count FROM slug_table;

    SET iteration = 0;

    START TRANSACTION;

        WHILE iteration < count DO

            INSERT INTO line_combinations
                SELECT REPLACE(`line`, '{a}', slugs[iteration]) AS `line`
                FROM line_combinations
                WHERE `line` LIKE CONCAT('%/', '{a}', '%');

            SET iteration = iteration + 1;

        END WHILE;

    COMMIT;


END
$$
DELIMITER ;

Btw, I don't want to use any external programming language to make this, this procedure will be working for billions of rows. I read Loops in SQL is not a good way due to performance concerns.

If you suggest another way I would accept this also.

I asked another detailed question but couldn't get an answer. if you would like to check that also : https://stackoverflow.com/questions/35320494/fetch-placeholders-from-table-and-place-into-generated-line-combination-pattern

StanislavL · Accepted Answer · 2016-02-11 13:20:34Z

1

So for each line with {a} you need to insert COUNT(*) from slug_table times values filled with slug value.

It seems you can do that just in one INSERT from SELECT

INSERT INTO line_combinations
            (SELECT REPLACE(lc.line, '{a}', st.slug) AS `line`
            FROM line_combinations lc, slug_table st
            WHERE lc.line LIKE CONCAT('%/', '{a}', '%');

UPDATE:

You can create a temp table line_combinations2 and insert all the records

FROM line_combinations 
WHERE line LIKE CONCAT('%/', '{a}', '%')

into the temp table. Then just use the temp table in the INSERT instead of original one

edited Feb 11, 2016 at 13:20

answered Feb 11, 2016 at 11:41

StanislavL

57.5k9 gold badges75 silver badges100 bronze badges

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

3 Comments

FZE Over a year ago

now it says Can't reopen table: line_combinations I think it locks the table while inserting, streaming doesn't work.

StanislavL Over a year ago

you can create a temp table line_combinations2 and insert all the records FROM line_combinations WHERE line LIKE CONCAT('%/', '{a}', '%') into the temp table. Then just use the temp table in the INSERT instead of original one

FZE Over a year ago

you are correct there are 5 placeholders like {a} stackoverflow.com/questions/35320494/… I think there would be 5 or 6 temporary tables, anyway I think there isn't make this query more simpler :) Could you please update your answer due to this change I accept your answer.

Collectives™ on Stack Overflow

Mysql Loop through selected column and execute Insert query in the loop

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related