Geraint Anderson Geraint Anderson - 1 month ago 5
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:

**person**
id
name

**film**
id
title

**person_film**
personId
filmId


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

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.

Comments