CODEWITHSUNDEEP
phpmysqlsql
phantomCoder
phantomCoder

Reputation: 1587

mysql query help insert into multiple rows with few fields same

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.

Upvotes: 3

Views: 293

Answers (3)

Gumbo
Gumbo

Reputation: 655189

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.

Upvotes: 1

Sebas
Sebas

Reputation: 21522

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

Upvotes: 2

Sirko
Sirko

Reputation: 74036

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

Upvotes: 1

Related Questions