MySQL foreach loop

Question

I have to iterate each row in my table User in MySQL. I need to create a new row Address for each iteration in User with some conditions described below.

I have 3 tables:

User: id, stuff, id_person, email
Person: id, stuff, id_address
Address: id, email

I need to create a new row in Address if the User.id_person is NOT NULL and that person.id_address IS NULL. I have to create the row with the same email that User.email. I have to do that for each row in User.

I tried to use MySQL cursor's but I do not know how to use them very well.

How can I do that? Is there any other way instead of using cursor's for that?

Thanks in advance.

EDIT: I have just realized that I also have to update person.id_address with the id of the address' row I have just created.

Lookup INSERT ,,, FROM SELECT. If you can construct a SELECT statement that results in the "rows" needed to do the insert into Address, that will do the job without having to resort to a foreach loop. — Sloan Thrasher
– Sloan Thrasher, Commented Mar 12, 2018 at 16:19
can you post the code that you've tried, w/ the cursor? this should be a fairly easy task. if i'm analyzing your data right, i don't think an iteration is necessary, a simple INSERT INTO w/ conditionals should suffice. — Napoli
– Napoli, Commented Mar 12, 2018 at 16:22

Napoli · Accepted Answer · 2018-03-12 17:28:48Z

From what I can gather, the following should suffice, so long as the fields are what you have provided.

INSERT INTO Address (email)
  SELECT User.email
    FROM User JOIN person ON User.id_person = person.id
   WHERE person.id_address IS NULL
;

EDIT (with Cursor)

This should be pretty simple with a cursor, however I highly advise you familiarize yourself with these and the implications.

DROP PROCEDURE IF EXISTS _tmp_update_address;
DELIMITER $$
CREATE PROCEDURE _tmp_update_address()
BEGIN
   DECLARE cursor_List_isdone BOOLEAN DEFAULT FALSE;
   DECLARE cur_userId, cur_personId INT;
   DECLARE cur_email VARCHAR(250) DEFAULT '';

   DECLARE cursor_List CURSOR FOR 
      SELECT User.id, person.id_address, User.email
      FROM User JOIN person ON User.id_person = person.id
      WHERE person.id_address IS NULL
    ;

   DECLARE CONTINUE HANDLER FOR NOT FOUND SET cursor_List_isdone = TRUE;

   OPEN cursor_List;

   loop_List: LOOP
      FETCH cursor_List INTO cur_userId, cur_personId, cur_email;
      IF cursor_List_isdone THEN
         LEAVE loop_List;
      END IF;

      INSERT INTO Address (email) VALUES (cur_email);
      UPDATE person SET person.id_address = LAST_INSERT_ID()
         WHERE person.id = cur_personId;

   END LOOP loop_List;

   CLOSE cursor_List;
END

$$

DELIMITER ;

CALL _tmp_update_address();

John · Accepted Answer · 2021-11-12 20:53:06Z

4

When I search "mysql foreach loop" this is what I found, and since I found a better alternative than the cursor for doing a foreach loop, here we are :

delimiter $$
create or replace procedure _tmp_procedure() begin
    for var_user in (
        select User.id, person.id_address, User.email
        from User join person on User.id_person = person.id
        where person.id_address is null
    ) do
        INSERT INTO Address (email) VALUES (var_user.email);
        UPDATE person SET person.id_address = LAST_INSERT_ID()
            WHERE person.id = var_user.id_person;
    end for;
end;
$$

call _tmp_procedure();

edited Nov 12, 2021 at 20:53

answered Nov 12, 2021 at 20:07

John

4363 silver badges10 bronze badges

3 Comments

Parthanon Over a year ago

Syntax doesn't work with mysql.

John Over a year ago

Ow. My bad. I probably wrote it with a MariaDB syntax ( •~•)

StefanBD Over a year ago

im was here looking for a loop and I'm using mariadb and found only complex stuff, nice thank you very much u save me a lot of work and complex stuff :)

Gordon Linoff · Accepted Answer · 2018-03-12 16:24:47Z

2

You don't want to use a cursor for this. Based on what you describe:

insert into address (address)
    select u.email
    from user u join
         person p
         on u.id_person = p.id;

answered Mar 12, 2018 at 16:24

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

Collectives™ on Stack Overflow

MySQL foreach loop

3 Answers 3

Comments

3 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

3 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related