Updating records selected using information from another table

Introduction 

This isn't that common a task, but it is a bit of a pain to work out exactly how to do it - there are various examples I found on the web but they all mostly seem to  rely on

SELECT...FROM (SELECT...FROM (SELECT...

Which is not a particularly elegant solution.

It's fairly obvious  that you can do it with JOIN, but only once you've actually done it...:laugh: 

Background  

Suppose you have a video library and it has a single table which records each movie or TV program by name. If you are like me, some of these are in series: "Star wars" films, or "House MD" episodes for example.

So, you might have a table for the video

Videos:
  Id        GUID
  Name      VARCHAR(200)  

And a separate table for series (because the series name may not be part of any movie / programme name)

In fact, let's have two more tables:

Series:
  Id        GUID
  Name      VARCHAR(200)

Episode:
  Id        GUID
  SeriesId  GUID
  VideoId   GUID
  SeriesNo  INT
  EpisodeNo INT

(and don't ask "why are you using GUIDs instead of INTs?" - go and use Google!)

So the Episode table "bridges" the Video and Series table, and add some information which is specific to an  Episode of a Series. 

So, you have all this set up, and then you realise: all your House MD episodes are named with just the episode name:

"Everybody Lies"
"Paternity"
"Occam's Razor"
"Maternity"
"Damned If You Do"
"The Socratic Method"
...

And you wanted them as:

"House MD - Everybody Lies"
"House MD - Paternity"
"House MD - Occam's Razor"
"House MD - Maternity"
"House MD - Damned If You Do"
"House MD - The Socratic Method"
...

Noooo! The horror, the horror... 

Using the Code 

It's not that bad - all you have to do is use an UPDATE statement with a JOIN (or two, in fact)

UPDATE Videos SET Title='House MD - ' + Title
FROM Videos v
JOIN Episode e ON v.Id=e.VideoId
JOIN Series  s ON s.Id=e.SeriesId
WHERE s.Name ='House MD'

How the code works  

Working backwards through this:

JOIN Series  s ON s.Id=e.SeriesId
WHERE s.Name ='House MD'

Selects only the row in the Serials table with a matching name: so the Series Id will be the one (and only the one) that we want. 

JOIN Episode e ON v.Id=e.VideoId

Limits the rows to those that have a matching VideoId and SeriesId to those in the Episodes table. 

UPDATE Videos SET Title='House MD - ' + Title
FROM Videos v

Updates only the rows which match all the criteria:

Video Id exists in the Episode table, and the Episode table row also has a matching Series Id to that selected in the Series table by having the name "House MD"

So only the videos in the "House MD" series are affected.  

Points of Interest 

Before you try anything which updates multiple records, I strongly suggest backing up your DB first...

A way to do it in C# is described here: Backing up an SQL Database in C#[^]

History 

Original Version