Insert statement using variable in sql query

user_10453238 0
Viewed 45

I am using mysql(5.7.14). I have a user table and a experiment table.

For the user table, it has columns:

user_id | user_name| email

For the experiment table, it has column:

user_id | experiment_id | created_at

I need to:

  • get 10% of random user, so I have something like this:
SELECT user_id from user where rand() <= 0.1
  • insert the user from the above into the experiment table, with experiment_id = 1

So I am wondering if there is a way to define a variable like:

user_ids = SELECT user_id from user where rand() <= 0.1

And then use it in my insert statement:

INSERT INTO experiment VALUES (user_ids, '1', DEFAULT);

sql-insert mysql sql
1 Answers

You could use an insert-select statement:

INSERT INTO experiment
SELECT      user_id, '1', DEFAULT
FROM        user
WHERE       RAND() <= 0.1
user_24227760
Related