Question 172050 in Mysql Question posted in
The official MySQL documentation can be found here.

“How to insert multiple values into a table using a SELECT subquery in MySQL?”

I am building a db with MySQL and would like to add several records at the same time, using a SELECT subquery to find values from a different table.

This query inserts a track into the tbl_track and checks if the release it belongs (album) (in tbl_release) has more than 1 format, CD or Vinyl. In this case, I have the album in Vinyl, Digital and CD formats.

When I use this following query I get an error "Subquery returns more than 1 row".

INSERT INTO tbl_track (trackname, trackno, tracklength, releaseid) 
VALUES('Fluffy Tufts','2',3.6,(SELECT releaseid 
                               FROM tbl_release 
                               WHERE releasename = 'Victorialand' 
                                 AND releaseformat IN ('CD','V')));

Do I have to break this query into 2 queries, inserting 1 track for Vinyl format and another for CD format?

Thanks.

Tags:

2

Answers


  1. 0

    You can also remove the VALUES operator and use INSERT INTO ... SELECT, while moving your constants inside the query.

    INSERT INTO tbl_track (trackname, trackno, tracklength, releaseid)
SELECT 'Fluffy Tufts', 
       '2',
       3.6, 
       releaseid 
FROM tbl_release 
WHERE releasename = 'Victorialand' AND releaseformat IN ('CD','V')));
    Login or Signup to reply.
  2. 0

    Try this by removing values from your query

    INSERT INTO tbl_track (trackname, trackno, tracklength, releaseid)
    SELECT 'Fluffy Tufts','2',3.6, releaseid 
    FROM tbl_release 
    WHERE releasename = 'Victorialand' AND releaseformat IN ('CD','V')));
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search