2

I'm a SQLite and SQL beginner so please bear with me. I'm trying my hand a creating a simple podcast index database. Here's the schema for my EPISODE, CAST_MEMBER, and EPISODE_CAST tables.

CREATE TABLE EPISODE
(
    id                  INTEGER PRIMARY KEY,
    show_number         INTEGER UNIQUE NOT NULL,
    air_date            DATE UNIQUE NOT NULL,
    runtime             VARCHAR(255) NOT NULL
);

CREATE TABLE CAST_MEMBER
(
    id          INTEGER PRIMARY KEY,
    person_name VARCHAR(255) UNIQUE NOT NULL
);

CREATE TABLE EPISODE_CAST
(
    episode_id      INTEGER NOT NULL,
    cast_member_id  INTEGER NOT NULL,
    PRIMARY KEY(episode_id, cast_member_id),
    FOREIGN KEY(episode_id) REFERENCES EPISODE(id),
    FOREIGN KEY(cast_member_id) REFERENCES CAST_MEMBER(id)
);

I'm trying to find a quick way to populate the EPISODE_CAST table. I know what all the relationships should be, I just don't want to have to write a million insert statements. For example, one cast member has been on every episode of the show so I naturally want 550 rows added to the table

episode_id | cast_member_id
===========================
1          | 1
2          | 1
3          | 1

… and so on.

So here's the query that I've written

INSERT INTO EPISODE_CAST (episode_id, cast_member_id) VALUES
   ((SELECT id FROM EPISODE WHERE show_number BETWEEN 1 AND 550),
   (SELECT id FROM CAST_MEMBER WHERE person_name='John Smith'));

But I'm only ever getting one row inserted. (The 1|1 row.) So I'm obviously missing something here. A loop of some sort…

Once I have that one cast member in there, I'd also like to do something similar with the other cast members. But for them I'll be using an IN clause with the set of all the episodes that person was in (most are non-contiguous).

Again, sorry for my naïvety. But thanks for any help.

1
  • You should look at using a JOIN statement. SELECT the rows you want, using a JOIN, and use that as your INSERT. Aug 14, 2016 at 22:32

1 Answer 1

3

Try a Cartesian or cross join. You can test the select clause separately to ensure it selects the desired data before adding it into the insert.

INSERT INTO EPISODE_CAST (episode_id, cast_member_id)
SELECT  episode.id AS episode_id,
        cast_member.id AS cast_member_id
FROM EPISODE CROSS JOIN CAST_MEMBER 
WHERE show_number BETWEEN 1 AND 550
AND   person_name='John Smith';
4
  • Almost there! I ran the select part separately and it grabbed everything I wanted. But when I run the whole insert I'm getting a syntax error: "Error near "select": syntax error". Unfortunately, the error isn't any more descriptive than that. Any ideas?
    – Matthew J
    Aug 15, 2016 at 2:30
  • @MatthewJ It looks like the brackets around the select shouldn't be there. I've updated my answer.
    – BillThor
    Aug 15, 2016 at 2:48
  • I got it working with one minor change: removing the word VALUES from the statement. If you can edit your answer to do that, I'll mark it as correct. Thanks so much!!
    – Matthew J
    Aug 15, 2016 at 3:23
  • @MatthewJ Editted.
    – BillThor
    Aug 15, 2016 at 3:27

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.