Using an INNER JOIN on a SQL UPDATE Statement

I made a slight blunder in my application that crept up yesterday at work. We have an internal task tracking application and I’ve been writing some supplementary web pages to interface with the application.

It was discovered that in my code, I was incorrectly assigning the department Id of the user when inserting a new time track record into the database. I should have been inserting the Id of the requesting department instead, so that the proper time would be billed.

There were about 1500 records that had been affected over a period of a month or so. While this wasn’t drastic, it was a sufficiently large enough count that it would take me too long to manually update all the records. Some of the time tracking records did have the proper department assignment, since the main web page was also being used to enter time. Finding the bad records in question was simple enough to do:

SELECT dbo.TimeTrack.Id AS TimeId,
       dbo.Task.Id As TaskId,
       dbo.TimeTrack.IdDepartment As TimeDept,
       dbo.Task.IdDepartment AS TaskDept
  FROM dbo.TimeTrack
       INNER JOIN dbo.Task ON dbo.TimeTrack.IdTask = dbo.Task.ID
 WHERE dbo.TimeTrack.IdDepartment <> dbo.Task.IdDepartment

However, updating the records in a SQL statement was a little bit trickier, at least to this non Jedi/Ninja SQL guy. After a little bit of tweaking and testing, I found that I could use an INNER JOIN as part of an UDPATE statement, you just had to “phrase” the INNER JOIN through the FROM clause of the statement. A generic form of this statement looks like this:

UPDATE TableA
   SET TableA.Column = TableB.Column
  FROM TableA
       INNER JOIN TableB ON TableA.IdForeign = TableB.Id

In my case, with the additional WHERE clause to find the mismatching the departments, I was able to update the records quickly with this query:

UPDATE dbo.TimeTrack
   SET dbo.TimeTrack.IdDepartment = dbo.Task.IdDepartment
  FROM dbo.TimeTrack
       INNER JOIN dbo.Task ON dbo.TimeTrack.IdTask = dbo.Task.Id
 WHERE dbo.Task.IdDepartment <> dbo.TimeTrack.IdDepartment

Just another blade to add to your ninja skills, force trick to add to your Jedi skills, or tool to add to your trunk if you don’t have it already. Enjoy!

What are your 10 bits on the matter? I want to know!

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s