CODEWITHSUNDEEP
mysqlinsertsubquery
devang
devang

Reputation: 4527

insert query with a subquery

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..

Upvotes: 2

Views: 13425

Answers (6)

valli
valli

Reputation: 5931

Subquery return values must match with insert query as below.

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;

Upvotes: 1

toast38coza
toast38coza

Reputation: 9086

You are asking it to add values for 3 columns (person_id, song_id, role_id), but are only supplying 1:

person_id

Try:

insert into filmo_person_song (person_id)
values (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);

or:

insert into filmo_person_song (person_id, song_id, role_id)
values (
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);

Upvotes: 1

Jimmy Shelter
Jimmy Shelter

Reputation: 1540

Change it to:

insert into filmo_person_song (person_id, song_id, role_id)
select person_id, fs.song_id, fr.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;

Upvotes: 1

David Hedlund
David Hedlund

Reputation: 129832

you're just specifying one field ( select person_id ) to insert into the table, while the column specifications state that person_id, sond_id and role_id will be inserted.

this should work better:

insert into filmo_person_song (person_id, song_id, role_id)
select person_id, fs.song_id, fr.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;

Upvotes: 2

Tatu Ulmanen
Tatu Ulmanen

Reputation: 124888

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;

Upvotes: 2

Jørn Schou-Rode
Jørn Schou-Rode

Reputation: 38406

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
       ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^

Upvotes: 8

Related Questions