Jump to content
Sign in to follow this  
MaxMichel

Update a table with an Order By statement

Recommended Posts

I have a diagram representing a database and need to find a way to update a given table with respect to information about elements in that table. 

Here is the database design: https://i.stack.imgur.com/HR6q2.jpg

I have a Playlist defined which is called "Frank's Stuff" which is owned by the Customer Frank Harris.

The question is the following:

Update the playlist "Frank's Stuff" as a function of the track duration. 
Display this playlist with the TrackID, Name and Milliseconds to verify.

I've read that you cannot use an ORDER BY statement with and UPDATE statement unless it is enclosed in a SELECT statement.

I'm not quite sure how to do all this though and would greatly appreciate some help.

Thanks!

Share this post


Link to post
Share on other sites
On 4/25/2019 at 6:41 AM, MaxMichel said:

Update the playlist "Frank's Stuff" as a function of the track duration. 
Display this playlist with the TrackID, Name and Milliseconds to verify.

This is a little bit vague to offer any suggestions. I'm not quite sure what you're asking here.
The second half looks like it can be described as such

SELECT Track.TrackId, Track.Name, Track.Milliseconds
FROM Track 
JOIN PlaylistTrack ON (Track.TrackId=PlaylistTrack.TrackId) 
JOIN Playlist ON (PlaylistTrack.PlaylistId=Playlist.PlaylistId) 
WHERE Playlist.Name = 'Frank\'s Stuff'
On 4/25/2019 at 6:41 AM, MaxMichel said:

I've read that you cannot use an ORDER BY statement with and UPDATE statement unless it is enclosed in a SELECT statement.

That's probably because you can't ORDER BY an UPDATE statement. UPDATE doesn't return a resulting set to ORDER BY. Its a command like INSERT or DELETE.

Share this post


Link to post
Share on other sites

There's not really a reason to use ORDER with UPDATE, it shouldn't matter what order the rows get updated.

Update the playlist "Frank's Stuff" as a function of the track duration. 

What does that mean?  Is the exercise to append the total duration to the playlist title?

Share this post


Link to post
Share on other sites

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Paste as plain text instead

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

Loading...
Sign in to follow this  

×
×
  • Create New...