CODEWITHSUNDEEP
mysqldatabaseloopsmysql-logic
Gunnrryy
Gunnrryy

Reputation: 350

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

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.

Upvotes: 0

Views: 28

Answers (2)

codeslayer1
codeslayer1

Reputation: 3686

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

Upvotes: 0

Lamar
Lamar

Reputation: 1849

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

Upvotes: 0

Related Questions