Create a new row in table 1 from data in table 2


#1

Hi everyone,

I'm a stuck with the 2nd exercice in last project of SQL course (querying tables):
"Smooth Criminal" is a track from Michael Jackson's "Bad" album. Add this track to the database.

This code does work, but I have to check manually what is the album_id (in the albums table) :

INSERT INTO tracks(title, album_id)
VALUES ('Smooth Criminal', 8) ;

I would like to do it automatically, more or less like this ( but this code does not work) :

INSERT INTO tracks(title, album_id)
VALUES ('Smooth Criminal', SELECT id
FROM albums
WHERE name = 'Bad') ;

Does anyone know the correct syntax to do this operation ?
Thanks a lot.

Annabelle


#2

Replying my own question hehe... I finally found the answer !
I write it here so it may help some others.

It's very simple, only some ( ) missing :

INSERT INTO tracks(title, album_id)
VALUES
(
'Smooth Criminal',
(SELECT id FROM albums WHERE name = 'Bad')
) ;

#3

Glad you found the answer, this is really interesting to me. I knew how to do it by looking for the number, but I wanted to know the same as you, so thanks for answering your question. :slight_smile: Awesome job.


#4

i was stuck in the same question , tks for the reply :slight_smile: