CODEWITHSUNDEEP
phpmysqlphpmyadmin
Nickelbids
Nickelbids

Reputation: 11

How to query one table and add rows to another using that first query? MySQL

I have some users setup in a MySQL table with different variables. I am trying to figure out what would be the best way to do this. Basically I want to award all of my registered and active users with bids which are stored in another table.

So for the Table "users" I have ran this query:

SELECT *  
 FROM `users` 
WHERE `active` = 1 
  AND `admin` = 0 
ORDER BY `users`.`id` ASC

Which will show all active users who are not administrators.

Now I would like to give each one of these users which are identified by the "ID" field in another table.

So in the "bids" table I would need to add a new row for each one of those users with all of the same values except for the "user_id" field which will basically match the "id" field of the table "users"

What would be the best approach for this. There are approximately 6,000+ users coming up in the first query.

Upvotes: 0

Views: 895

Answers (2)

Dark Castle
Dark Castle

Reputation: 1299

Typically not a good idea to duplicate data like that but there is also:

create table bids as select <fields> from users

Upvotes: 0

Randy Simon
Randy Simon

Reputation: 3324

Can you do something like this? 

INSERT INTO bids 
   (col1, col12, col3)
SELECT
   users.col1, users.col2, users.col3 FROM users
WHERE users.active = 1 and users.admin = 0
ORDER BY users.id ASC

Upvotes: 1

Related Questions