query from db for multiple rows and loop over it to insert for each record

Question

my issue is i want to query x rows from tbl_one and loop over the result and insert values into other table

select id as user_id, p_id from users where b_id = 0 // this will give me let's say 10 rows

and now i want to use each of the rows to insert into address table like

insert into addresses (user_id, passenger_id, address_type, address_line1, city, country) value (user_id, passenger_id, 'Test street address', 'XXX', "XX")

how can i do this in mysql. tried searching in mysql documentation from https://dev.mysql.com/doc/refman/5.7/en/loop.html but there the looping logic explained is very static and i didn't understand.

Answer 1

Use INSERT INTO SELECT statement:

INSERT INTO addresses (user_id, passenger_id, address_type, address_line1, city, country) 
SELECT id, p_id, address_type, 'Test street address', 'XXX', 'XX')
FROM users 
WHERE b_id = 0 

Answer 2

If you want to keep the other colums like address_type etc as static then you can use below query:

INSERT INTO addresses (user_id, passenger_id, address_type, address_line1, city, country) 
SELECT id, p_id, "Test Address Type", 'Test Address', 'Test City', 'Test Country')
FROM users 
WHERE b_id = 0