Simple Complex SQL Updates – Updating a Derived Table

When amending data in a large SQL database it makes sense that the bread-and-butter command is the classic UPDATE statement. this statement is reasonably simple and has the following syntax:
(the MSDN link is here: https://msdn.microsoft.com/en-us/library/ms177523.aspx )

UPDATE TABLE_NAME
 SET column1=value1,column2=value2,...
 WHERE some_column=some_value;

And this works brilliantly for single table inserts but when you’re faced with a situation that you need to join to a number of tables this can get a little more tricky, you’ll need to make sure that everything is aliased properly and that you’ve told it to update the right table – unless you’ve crafted your queries carefully whilst digging around the database to see what you need to do, there will be another piece of work to create the update statement once you’ve completed your analysis.

Not so much anymore…

Derived tables and Common table expressions are wonderful ‘shorcuts’ in SQL, I’ve mentioned them before and for one off jobs they’re lovely, it’s definitely worth seeing if there is a way of achieving what you’d like to in a different way for creating functionality as there can be a performance hit but for individual tasks they can be wonderful and there’s a lovely way to use them in conjunction with update statements as below. Simply make up a statement that lists any information that you may want to see but specifically pull out the value(s) to be replaced into a column labeled ‘OldVal’ and put the data that you want to replace it with into a column called ‘NewVal’. If we were to want to extend the expiry date of ‘Premium’ members within a database whilst pulling out some detail for testing, to get the values that we’d like the code would look something like this:

SELECT m.DateExpires AS OldVal
, dateadd(MONTH, 1, m.DateExpires) AS NewVal
, c.Forename, c.Surname, c.CustomerRef
FROM Membership m
JOIN Customer c ON c.customerid = m.customerid
WHERE MebType = 'Premium'

This is great for passing over population selections for authorisation or testing and a good way of knowing exactly what you’re going to update before you do it. Once you have this script set up it’s a trivial thing to arrange your update statement, simply top and tail your select statement with the update script listed below:

UPDATE F
SET OldVal = NewVal
FROM (
      SELECT m.DateExpires AS OldVal
      , dateadd(MONTH, 1, m.DateExpires) AS NewVal
      , c.Forename, c.Surname, c.CustomerRef
      FROM Membership m
      JOIN Customer c ON c.CustomerID = m.CustomerID
      WHERE MebType = 'Premium'
     ) F

I find that it’s useful to leave the derived table on separate lines so that it can easily be run individually but this method gives a good amount of control over update statements without having to replicate code endlessly.

Enjoy!