MySQL Stored Procedure Loop

Question

So I'm needing to pull 2 id fields from 2 different tables and insert them into a third table. This is for a permissions thing, so basically I need to get the admin table's id and loop that until all of the building table's ids are populated into the third table called building_user. This is to "start" the permissions table so I need every building ID to be matched up with every admin ID.

Here is what I'm trying and I can't quite figure it out, but I know I'm missing something.

CREATE PROCEDURE buildingUserAssignment()
BEGIN
  DECLARE done INT DEFAULT 0;
  DECLARE done2 INT DEFAULT 0;
  DECLARE a, b INT;

   DECLARE admin CURSOR
   FOR
   SELECT id FROM admin;
   DECLARE building CURSOR
   FOR
   SELECT id FROM building;

   DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done=1;


  OPEN admin;
  OPEN building;
  REPEAT
    FETCH admin into a;

    REPEAT
    FETCH building into b;
    INSERT INTO building_user VALUES (a,b);
    UNTIL done END REPEAT;

  UNTIL done END REPEAT;


  CLOSE admin;
  CLOSE building;
END;

I end up only getting one admin ID field (ID #2, even though there is a ID 1). Here is the sample databases:

admin:

SELECT id FROM admin;
1
2
3
5
6
7
9
11

building:

SELECT id FROM building;
0
1
3
6
7
8
10

Current End Result of invoking buildingUserAssignment:

SELECT * FROM building_user:
user_id building_id
2   0
2   1
2   2
2   3
2   4
2   4
3   4
5   4
6   4
7   4
9   4
11   4

So the building_user table should be something like this in the end, repeated of course for each admin id:

I'm sure its something stupid, but I think I've looked at it too long and now I'm stuck. I may not even be doing this the "best" way either, so I welcome any suggestions.

Really pointless to use a loop for this. Anyway, this question is confusing... how is the admin related to a building? — Aluan Haddad
– Aluan Haddad, Commented Feb 7, 2018 at 1:21
This is to "start" the permissions table so I need every building ID to be matched up with every admin ID. — sensualstares
– sensualstares, Commented Feb 7, 2018 at 1:59
select into building_user user_id, building_id from building cross user or something similar. — Aluan Haddad
– Aluan Haddad, Commented Feb 7, 2018 at 2:02

John Woo · Accepted Answer · 2018-02-07 02:10:55Z

2

You don't need to use loop. There is a type of join called CROSS JOIN which produces a result set from number of rows from first table multiplied by the number of rows of the second table. And the resulting rows will be insert into INSERT INTO..SELECT syntax.

INSERT INTO building_user 
SELECT a.id, b.id
FROM admin a CROSS JOIN building_user b

answered Feb 7, 2018 at 2:10

John Woo

265k70 gold badges509 silver badges500 bronze badges

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

1 Comment

sensualstares Over a year ago

Holy cow, that was about 2 million times easier! I'm new to messing with the DB directly so I didn't know about this. I could've written something in PHP but wanted to do it in the DB directly as I thought it would be best to start doing things there. Thanks again!

Nikhil Sharma · Accepted Answer · 2021-08-10 09:12:30Z

0

WHILE i < v_count DO
    SET @start_date = JSON_UNQUOTE(JSON_EXTRACT(v_current_item, '$.start_date'));
    SET @expiry_date = JSON_UNQUOTE(JSON_EXTRACT(v_current_item, '$.expiry_date'));
    SET @amount = JSON_UNQUOTE(JSON_EXTRACT(v_current_item, '$.amount'));
    SET @emp_id = JSON_UNQUOTE(JSON_EXTRACT(v_current_item, '$.emp_id'));
    SET @update_id = JSON_UNQUOTE(JSON_EXTRACT(v_current_item,'$.update_id'));
                    SET i := i + 1;

 END WHILE;

answered Aug 10, 2021 at 9:12

Nikhil Sharma

12 bronze badges

1 Comment

Adrian Mole Over a year ago

While this code may solve the question, including an explanation of how and why this solves the problem would really help to improve the quality of your post, and probably result in more up-votes. Remember that you are answering the question for readers in the future, not just the person asking now. Please edit your answer to add explanations and give an indication of what limitations and assumptions apply.

Collectives™ on Stack Overflow

MySQL Stored Procedure Loop

2 Answers 2

1 Comment

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related