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...
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;
Maya is a hardware enthusiast who tests and reviews smart home devices, smartphones, wearables, and audio gear. She focuses on practical consumer value and build quality.
