Jump to content

Update a table with an Order By statement


MaxMichel
 Share

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!

Link to comment
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.

Link to comment
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?

Link to comment
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
 Share

×
×
  • Create New...