CODEWITHSUNDEEP
mysqldatabasesubquerysql-insert
Noob
Noob

Reputation: 1127

Insert row in table for each id from another table

Basically I have two tables like these:

Table 1: users

id_user, name, ...

Table 2: leaves

id_2, employee (the column employee is the id of the user from the first table), ...

Right now the table two is empty (doesn't have rows) and for every user from the first table I want to create a row on the second one inserting the id from the first table as a value in the column employee, like:

Table 2: leaves

id    employee    column1    column2    column3    column4    column5   
id1   1           date1      date2      integer1   integer2   string
id2   2           date1      date2      integer1   integer2   string
...

The INSERTS I tried:

  1. This one works fine:

    INSERT INTO entitleddays (employee, startdate, enddate, type, days, description)
VALUES (1, '2015-01-01', '2015-12-31', 3, 5, 'test');

  2. Here I tried what I explained above, but it doesn't work:

    INSERT INTO entitleddays (employee, startdate, enddate, type, days, description)
VALUES ((SELECT id from users), '2015-01-01', '2015-12-31', 3, 5, 'test');

I get the following error:

#1242 - Subquery returns more than 1 row

I do understand the error: the subquery is finding more than one result so it cannot insert the value into employee, my problem is that I don't know what syntax should I use as the noob I am. I just want to create a row in the second table for every row from the first table (using the id from the first table).

Upvotes: 4

Views: 6841

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269603

Just use insert . . . select:

INSERT INTO entitleddays (employee, startdate, enddate, type, days, description)
    SELECT id, '2015-01-01', '2015-12-31', 3, 5, 'test'
    FROM users;

Upvotes: 18

Related Questions