Click here to Skip to main content
13,146,934 members (72,413 online)
Click here to Skip to main content
Add your own
alternative version

Tagged as


3 bookmarked
Posted 14 Jul 2013

Updating records selected using information from another table

, 14 Jul 2013
Rate this:
Please Sign up or sign in to vote.
If you have two (or more) tables which are interrelated, you sometimes need to update the rows of one table, selected from information in a different table. This isn't difficult, but is can take a little think about.


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


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: 


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

  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:

  Id        GUID
  Name      VARCHAR(200)
  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"
"Occam's Razor"
"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#[^]


Original Version


This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)


About the Author

Wales Wales
Born at an early age, he grew older. At the same time, his hair grew longer, and was tied up behind his head.
Has problems spelling the word "the".
Invented the portable cat-flap.
Currently, has not died yet. Or has he?

You may also be interested in...


Comments and Discussions

-- There are no messages in this forum --
Permalink | Advertise | Privacy | Terms of Use | Mobile
Web02 | 2.8.170915.1 | Last Updated 14 Jul 2013
Article Copyright 2013 by OriginalGriff
Everything else Copyright © CodeProject, 1999-2017
Layout: fixed | fluid