Database Reference
In-Depth Information
Sheryl Crow for inserting into the SONG row. This statement would add a
new song by Sheryl Crow.
INSERT INTO SONG(SONG_ID,ARTIST_ID, TITLE)
VALUES(SONG_ID_SEQ.NEXTVAL
, (SELECT ARTIST_ID FROM ARTIST WHERE NAME='Sheryl Crow')
,'Where are you?');
The next command will update the song just inserted with a playing
time. The statement makes the PLAYING_TIME for “Where are you?”
equal to the PLAYING_TIME of the song named “Safe and Sound.”
UPDATE SONG SET PLAYING_TIME =
(SELECT PLAYING_TIME FROM SONG
WHERE TITLE = 'Safe And Sound')
WHERE TITLE = 'Where are you?';
Figure 12.15 shows the resulting inserted and subsequently updated row
for Sheryl Crow.
That completes this chapter on subqueries. The next chapter looks at
the more unusual or less used query types, including composites, hierarchi-
cal queries, flashback versions, and parallel queries.
Figure 12.15
Subqueries in
INSERT and
UPDATE
Statements.
