Insert statement using variable in sql query

Question

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:

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);

Mureinik · Accepted Answer · 2019-06-28 12:53:19Z

2

You could use an insert-select statement:

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

answered Jun 28, 2019 at 12:53

Mureinik

316k54 gold badges399 silver badges405 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Thank you! I will try it out :D