insert query with a subquery

Question

i tried to use the following query to insert data in a table and got an error

insert into filmo_person_song (person_id, song_id, role_id)
select person_id
from filmo_person_song fps, filmo_song fs, filmo_role fr
where fps.song_id = fs.song_id
  and fps.role_id = fr.role_id
  and fps.person_id = 43629;

ERROR 1136 (21S01): Column count doesn't match value count at row 1

i have specified the fields exactly..

Answer 1

You are trying to insert one value in each row, but you have specified three columns to be written to:

insert into filmo_person_song (person_id, song_id, role_id)
                               ^^^^^^^^^^^^^^^^^^^^^^^^^^^
select person_id
       ^^^^^^^^^

You will need to list values for all of the three columns in your select statement. The following might work:

insert into filmo_person_song (person_id, song_id, role_id)
select fps.person_id, fs.song_id, fr.role_id
       ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^

Answer 2

You are only selecting person_id from the subquery, whereas you specified person_id, song_id and role_id in the insert clause. You must select also the missing fields in the subquery.

You probably want something like this:

INSERT INTO
    filmo_person_song (person_id, song_id, role_id)
SELECT
    person_id,
    song_id,
    role_id
FROM 
    filmo_person_song fps, filmo_song fs, filmo_role fr
WHERE
    fps.song_id = fs.song_id
AND
    fps.role_id = fr.role_id
AND
   fps.person_id = 43629;