Yes! You Can Update with CTEs!

In my previous post, I discussed the basics of CTEs (Common Table Expressions). (See “Demystifying CTEs“) In that post, I had mentioned that you can use CTEs to insert, update, and delete records in addition to just retrieving them. So that’s what this post is about. Updating your records using a CTE.

Updating records using a CTE is actually pretty simple to do. For my first example below, let’s say you are a manager and your oh-so-awesome employees volunteered their Saturday to help another department out with some big task because they’re cool like that. Being the spectacular manager you are, you decide to grant more vacation time to your minions… err… employees. Of course, you will go through the proper channels via Human Resources, but you’re just curious what the numbers would look like and how much vacation time / comp time you will grant them. Disclaimer: I honestly don’t know how this scenario would work in real life since I’m not a manager, never have been, and after this  post, probably never will be. So please remember this is just an example and not based on any reality that I know of.

begin transaction
;with MyPeeps (EmployeeID, Title, ManagerID, VacationHours, DepartmentName)
 as
 ( select Employee.EmployeeID
         ,Employee.Title
         ,Employee.ManagerID
         ,Employee.VacationHours
         ,Department.Name DepartmentName
   from HumanResources.Employee
   inner join HumanResources.EmployeeDepartmentHistory
   on (Employee.EmployeeID = EmployeeDepartmentHistory.EmployeeID)
   inner join HumanResources.Department
   on (EmployeeDepartmentHistory.DepartmentID = Department.DepartmentID)
   where (Employee.ManagerID = 42
    and EmployeeDepartmentHistory.EndDate is null)
 )
 
 update MyPeeps
 set VacationHours = VacationHours + 10
 
 select  Employee.EmployeeID
        ,Employee.Title
        ,Employee.ManagerID
        ,Employee.VacationHours
        ,Department.Name DepartmentName
 from HumanResources.Employee
 inner join HumanResources.EmployeeDepartmentHistory
 on (Employee.EmployeeID = EmployeeDepartmentHistory.EmployeeID)
 inner join HumanResources.Department
 on (EmployeeDepartmentHistory.DepartmentID = Department.DepartmentID)
 where (Employee.ManagerID = 42
  and EmployeeDepartmentHistory.EndDate is null)
rollback

First of all, I wrapped the code in a transaction with a rollback so I don’t accidentally commit the update. If I didn’t use the rollback, let’s just say I had better be in the development system and leave it at that. In general, I like to do rollback transactions for any kind of data manipulation in case I do something stupid like forget a where clause and end up deleting all of the records instead of a few and then having to restore the entire table from a backup. Anyway, I put most of my logic into the CTE in this case. You don’t have to do that. You could put the where clause into your update statement instead of the CTE, if you wanted to and depending on your scenario. It’s something you can play around with. As you can see in the code, I’m actually using the name of the CTE in the update statement. It will actually update the vacationHours field in the Employee table. That is, it will update the table if you don’t use the rollback and commit it to the database.

So if you just run the select statement defining the CTE, your results may look something like this:

Example #1 - Inside the CTE

As you can see, your employees have a decent amount of vacation time. Let’s see what happens when we run the entire transaction.

Example #1 - After the Update

Hey, look at that! The numbers don’t look all that bad! Maybe you should give them extra vacation time. 😉

For argument’s sake, let’s say you think the code above may be a bit confusing to someone else. So you wonder if you can rewrite it to make it a bit more straightforward. Meaning, you’re not actually updating “MyPeep” but the Employee table and it’s not really all that clear. The code below shows how you can do that.

begin transaction
select  Employee.EmployeeID
       ,Employee.Title
       ,Employee.ManagerID
       ,Employee.VacationHours
       ,Department.Name DepartmentName
       ,Department.DepartmentID
 from HumanResources.Employee
 inner join HumanResources.EmployeeDepartmentHistory
 on (Employee.EmployeeID = EmployeeDepartmentHistory.EmployeeID)
 inner join HumanResources.Department
 on (EmployeeDepartmentHistory.DepartmentID = Department.DepartmentID)
 where Employee.ManagerID = 42
  and EmployeeDepartmentHistory.EndDate is null
 
 ;with MyPeeps (EmployeeID, DepartmentName, DepartmentID)
 as
 ( select EmployeeDepartmentHistory.EmployeeID
         ,Department.Name DepartmentName
         ,Department.DepartmentID
 from HumanResources.Department
 inner join HumanResources.EmployeeDepartmentHistory
 on (EmployeeDepartmentHistory.DepartmentID = Department.DepartmentID)
 where EmployeeDepartmentHistory.EndDate is null
 and Department.DepartmentID = 11
 )
 
 update HumanResources.Employee
 set VacationHours = VacationHours + 10
 from HumanResources.Employee
 inner join MyPeeps
 on Employee.EmployeeID = MyPeeps.EmployeeID
 and Employee.ManagerID=42;
 
 select Employee.EmployeeID
       ,Employee.Title
       ,Employee.ManagerID
       ,Employee.VacationHours
       ,Department.Name DepartmentName
       ,Department.DepartmentID
 from HumanResources.Employee
 inner join HumanResources.EmployeeDepartmentHistory
 on (Employee.EmployeeID = EmployeeDepartmentHistory.EmployeeID)
 inner join HumanResources.Department
 on (EmployeeDepartmentHistory.DepartmentID = Department.DepartmentID)
 where Employee.ManagerID = 42
  and EmployeeDepartmentHistory.EndDate is null
rollback

In this example, I included a select before the CTE because I changed the CTE definition. Basically, I put the same select statement before and after the CTE to show the data before and after the CTE. The CTE definition itself just joins the deparment table with the history table to get the employees in your department. For the update statement itself, I just join the MyPeeps CTE on the Employee table. It’s just a different way of accomplishing the same thing.

If you ran the select statement in the CTE by itself, you will see the employeeIDs in that specific department.

Example #2 - Inside the CTE

So if you ran the above example in its entirety, you should get two resultsets – one from before the update and for after the update.

Example #2 - Before
Example #2 - After the Update

See? It’s not that bad. 🙂  So what’s next? For my next post, I will write about how to insert records using CTEs. It’s pretty similar to updating. I could include it and deletions in this post, but I’m really trying hard to keep my posts from becoming lengthy novels. 🙂

Advertisements

6 thoughts on “Yes! You Can Update with CTEs!

Leave a Reply

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