mysql query help insert into multiple rows with few fields same

Question

I have doubt whether this is possible.

I have two tables

1) file_master

file_id  |  name
-----------------
1        | readme.txt
2        | readme2.txt
3        | readme3.txt
4        | readme4.txt
5        | readme4.txt


2) user_file

user_id  |  file_id
-----------------
1        | 2
1        | 4

I am looking for a query like this

INSERT INTO user_file (user_id, file_id) VALUES (1, SELECT file_id FROM file_master WHERE file_id <= 4);

this should be equivalent to

  INSERT INTO user_file (user_id, file_id) VALUES (1, 1);
  INSERT INTO user_file (user_id, file_id) VALUES (1, 2);
  INSERT INTO user_file (user_id, file_id) VALUES (1, 3);
  INSERT INTO user_file (user_id, file_id) VALUES (1, 4);

In PHP I can construct multiple Insert queries using a for loop. But I am looking for a pure SQL solution... if its possible.

Thanks in advance.

Sebas · Accepted Answer · 2012-06-02 19:22:00Z

2

INSERT INTO user_file (user_id, file_id)
    SELECT 1, file_id 
    FROM file_master 
    WHERE file_id <= 4;

answered Jun 2, 2012 at 19:22

Sebas

21.6k9 gold badges60 silver badges109 bronze badges

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

Comments

Sirko · Accepted Answer · 2012-06-02 19:21:19Z

1

INSERT INTO user_file (user_id, file_id) 
  SELECT 1, file_id FROM file_master WHERE file_id <= 4;

answered Jun 2, 2012 at 19:21

Sirko

74.3k19 gold badges157 silver badges194 bronze badges

Comments

Gumbo · Accepted Answer · 2012-06-02 19:22:16Z

1

You’re almost right:

INSERT INTO user_file (user_id, file_id) SELECT 1, file_id FROM file_master WHERE file_id <= 4;

See INSERT … SELECT statement.

answered Jun 2, 2012 at 19:22

Gumbo

657k112 gold badges792 silver badges852 bronze badges

Collectives™ on Stack Overflow

mysql query help insert into multiple rows with few fields same

3 Answers 3

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related