CODEWITHSUNDEEP
mysql
Shamoon
Shamoon

Reputation: 43531

How do I create a row in a table for each user in another table?

I have a table called users and I want to get all users and for each user, create a row in a table called products. Is it possible to do this with a query?

Upvotes: 0

Views: 69

Answers (2)

Sirko
Sirko

Reputation: 74046

Depending on the respective table structure an INSERT INTO ... SELECT statement should work:

INSERT INTO `products`
SELECT 1 FROM `users`

In case the products table has one int-column, this will insert as much ones as there are users in the users table.

You just have to adjust the SELECT statement to produce the values you need in your new table.

Example Fiddle

Upvotes: 3

Vatev
Vatev

Reputation: 7590

You can insert the result from a select statement like this:

INSERT INTO products (field1,field2...) SELECT 
    something_that_goes_into_products_field1,
    something_that_goes_into_products_field2,
    ....
FROM users
WHERE users_that_have_to_be_inserted

If products has an autoincrement field you should probably omit it from the field list.

Upvotes: 1

Related Questions