Inserting Data into a table using subquery

Question

I am trying to insert data using a subquery. I am only going to post my SELECT statement below.

SELECT PlaylistCode
FROM Playlists
WHERE Name_Of_Playlist = 'X'
AND 
(SELECT Code
  FROM Songs
  INNER JOIN Artists ON Artists.PageURL = Songs.PageURL
  WHERE Artist = 'X'
  LIMIT 200)

Error Code: 1242. Subquery returns more than 1 row 0.016 sec

In addition to missing EXISTS, your query is also missing aliases, which would have helped someone correct your syntax much faster. — Tim Biegeleisen
– Tim Biegeleisen, Commented Oct 16, 2019 at 1:51
@GordonLinoff Here is my Partial EERD: EERD Here is my list SongCode where Artist = ‘X’: Artist X — J S
– J S, Commented Oct 16, 2019 at 4:03
@GordonLinoff In Songs Table PageURL is a PK. In Artists Table, PageURL is a Foreign Key referencing Songs.PageURL. In Playlists table, SongCode is FK referencing Songs.Code. In PlaylistSongs table, PlaylistCode is referencing Playlists.PlaylistCode. In Playlists table, UserCode is referencing Auth_User.UserCode. I want to insert all Songs from Songs table to PlaylistSongs table where artist = 'X' to PlaylistSongs table where Name_of_Playlist = ‘Y’. I hope you can understand it now. — J S
– J S, Commented Oct 16, 2019 at 4:03

Kiran Patil · Accepted Answer · 2019-10-16 07:58:27Z

1

Try to Below Query

SELECT PlaylistCode
FROM Playlists
WHERE exits 
(SELECT Code
  FROM Songs
  INNER JOIN Artists ON Artists.PageURL = Songs.PageURL
  WHERE Artist = 'X'
  LIMIT 200) and Name_Of_Playlist = 'X'

answered Oct 16, 2019 at 7:58

Kiran Patil

3391 silver badge11 bronze badges

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

Comments

Ed Bangga · Accepted Answer · 2019-10-16 02:50:46Z

0

Based on your requirements, you need to use select case then join corresponding tables

  INSERT INTO Playlists (PlaylistCode)
    SELECT CASE WHEN a.Artist = 'X' THEN 'X' ELSE s.Code END   
    FROM Songs s
    INNER JOIN Artists a ON a.PageURL = s.PageURL

edited Oct 16, 2019 at 2:50

answered Oct 16, 2019 at 1:46

Ed Bangga

13k4 gold badges18 silver badges31 bronze badges

7 Comments

J S Over a year ago

I tried with exist & its only showing 1 result. My full query: INSERT INTO PlaylistSongs (PlaylistCode, SongCode) SELECT Playlists.PlaylistCode FROM Playlists WHERE Playlists.Name_Of_Playlist = 'Babbu Maan' AND EXISTS (SELECT Songs.Code FROM Songs INNER JOIN Artists ON Artists.PageURL = Songs.PageURL WHERE Artists.Artist = 'Babbu Maan' LIMIT 200)

Ed Bangga Over a year ago

removed limit 200, try again

J S Over a year ago

still same issue.

Ed Bangga Over a year ago

i need your data and expected output, for us to solve this out

J S Over a year ago

What I am trying to do is where ever the artist name = 'X' set the PlaylistCode to 'X' and insert all values to table PlaylistSongs

|

Collectives™ on Stack Overflow

Inserting Data into a table using subquery

2 Answers 2

Comments

7 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

7 Comments

Your Answer

Sign up or log in

Post as a guest

Related