MySQL Insert results of select

Question

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?

Strawberry · Accepted Answer · 2016-10-20 12:45:03Z

3

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.

edited Oct 20, 2016 at 12:45

Strawberry

34k14 gold badges43 silver badges58 bronze badges

answered Oct 20, 2016 at 12:43

Sami Kuhmonen

31.4k9 gold badges67 silver badges81 bronze badges

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

Comments

kiran madke · Accepted Answer · 2016-10-20 12:59:58Z

0

Use numeric literals with aliases inside a SELECT statement. No () are necessary around the SELECT component.

INSERT INTO person_film (personId, filmId)
  SELECT
    /* Literal number values with column aliases */
    1 AS fpersonId,
    2 AS filmId,

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

Note that in context of an INSERT INTO...SELECT, the aliases are not actually necessary and you can just SELECT 1, 2 but in a normal SELECT you'll need the aliases to access the columns returned.

answered Oct 20, 2016 at 12:59

kiran madke

12 bronze badges

Collectives™ on Stack Overflow

MySQL Insert results of select

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related