How to populate a mysql table with data from two other tables-CodePudding

I have two tables with a many-to-many relation, and a joint table between them, for example:

client (id, name)
address (id, address)
client_address (client_id, address_id)

I need to populate the client_address table with a line for every client, using a specific address, like:

client_id, address_id
1,         1
2,         1
3,         1
4,         1
etc...

I tried something like this (which obviously does not work):

INSERT INTO 
  client_address (`client_id`, `address_id`) 
  SELECT id from client, 
  SELECT id from address where address = 'My Address';

can I do this with a single query?

CodePudding user response：

If you have to populate it manually just like your example you can try to use CROSS JOIN :

INSERT INTO 
  client_address (`client_id`, `address_id`) 
  SELECT c.id, a.id 
  FROM client c, 
  CROSS JOIN address a 
  WHERE address = 'My Address';

This will create a line for every client you have in CLIENT table and the address you chose in the WHERE clause