MySQL foreach loop

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.

3 Answers

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(); 

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(); 

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;