SQL How do I insert into select the following data-CodePudding

I am trying to insert a FK into my playlistvideos table from playlist table. The following code should look sometime like this

INSERT INTO playlistvideos(videoID, playlistFK, videoTitle, videoLink, thumbnail) VALUES (2,playlistID,'JWD','JWD.mp4,'JWD.jpg')
SELECT playlist.playlistID
FROM playlist
WHERE playlist.userFK = 'Armistice' AND playlist.icon = 'Heart'

playlist:

playlistID | name      | userfk | icon
1          | Favourite | Armistice | Heart

videos:

videoID | videoTitle| videoLink | Thumbnail
2       | JWD       | JWD.mp4   | JWD.jpg

The Following output should be playlistvideos:

videoID | playlistFK| videoTitle | videoLink | Thumbnail
2       | 1         | JWD        | JWD.mp4   | JWD.jpg

CodePudding user response：

Use INSERT .. SELECT.

INSERT INTO playlistvideos (videoID, playlistFK, videoTitle, videoLink, thumbnail) 
SELECT 2, playlistID, 'JWD', 'JWD.mp4', 'JWD.jpg'
FROM playlist
WHERE userFK = 'Armistice' 
  AND icon = 'Heart'

SELECT may select both columns values and literal values.

CodePudding user response：

I think you are looking to insert the value of SELECT playlist.playlistID... into your playlistFK column

INSERT INTO playlistvideos(videoID, playlistFK, videoTitle, videoLink, thumbnail) VALUES (2,
(SELECT playlist.playlistID
FROM playlist
WHERE playlist.userFK = 'Armistice' AND playlist.icon = 'Heart'
),'JWD','JWD.mp4,'JWD.jpg')