I made a slight blunder in my application that crept up yesterday at work. We have an internal task tracking application and Ive 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 wasnt 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 dont have it already. Enjoy!