Geraint Anderson Geraint Anderson - 9 months ago 47
MySQL Question

MySQL Insert results of select

I have a many to many relationship and am trying to insert a new relationship. At the point I'm doing the insert I don't know the id so would need to look it up. My table structure looks something like this:




Given a person's id and a list of film titles (but not their ids) I'm only able to insert these relationships into the person_film table in two steps.

SELECT id FROM film WHERE title="film1" OR title="film2";

Then the results of that can be used in an insert:

INSERT INTO person_film (personId, filmId) VALUES (5, 5),(5, 7);

Is there a way of doing this in a single SQL statement?

Answer Source

You can do it with a subquery:

INSERT INTO person_film (personId, filmId)
  SELECT 5, id FROM film
    WHERE title IN("film1","film2");

Here the 5 is the personId value and the filmId will be retrieved from the film table.