Updating a Join Statement

Let's start with this case. You need to update some records in the database. So to find the records, you write the following SQL statement:

select *
from tblPersons p
inner join tblAppointments a on a.PersonID = p.PersonID
where a.PersonID = 'A0CD5C88-8737-4E8E-AEA7-9E28C809DE1F'

Cool! So now you have identified and verified the records that need to be updated. But now the update itself. Let's say the Completed column in the tblAppointments table should be set to 1.

That is not the most difficult thing to do. But sometimes in more complex cases I want the customer to see the current value and the new value side by side. And then get an approval by them that this update is indeed what should be done.

So for that, I usualy do the following:

-- first create a view
create view tmpAppointmentUpdater as
select a.AppointmentHID, a.Completed as CurrentValue, 1 as NewValue
from tblPersons p
inner join tblAppointments a on a.PersonID = p.PersonID
where a.PersonID = 'A0CD5C88-8737-4E8E-AEA7-9E28C809DE1F'

-- Show the results for approval
select * from tmpAppointmentUpdater

-- after approval, update the view
update tmpAppointmentUpdater
set CurrentValue = NewValue

-- Clean up the view
drop view tmpAppointmentUpdater

First I create the view and show the results from that to the customer. They can now check as many records as they like if the NewValue is really what they want. If so, I update the view and drop it directly to clean up.

But sometimes it isn't all that difficult, like in this case. So when I have the select statement, I can easily turn it into an update statement:

update a
set a.Completed = 0
from tblPersons p
inner join tblAppointments a on a.PersonID = p.PersonID
where a.PersonID = 'A0CD5C88-8737-4E8E-AEA7-9E28C809DE1F'

As you can see, the Where Clause stays exactly the same. But instead of a Select, I changed it into an Update statement.

You can update the Main Table from the Select. But, as shown in this example, it could also be one of the Join tables. The limitation however is that you can only update one Table at the time.

Not for any case but still useful in many cases. And a lot less code!

Dates and Numbers Tables

While making a Sumatra Report I needed to show a Bar Graph with the total Sales Revenue per Day. Not that difficult. But not every date had a Sales Revenue. Think of weekends, public holidays and well, also some disappointing days with no sales at all. Yikes.

Using the Financial Transaction table seemed to be a good starting point. But how to fill in the blank dates with no Sales Revenue? Ah, if only there was a table with every date in a given date range!

Hold on! What about the Recursive Common Table Expressions? Why not use its Anchor for the first date, and the Recursive part for the other dates until we reach the end date?

Something like this:

with dates as (
  select cast('2020-01-01' as DateTime) as ReportDate
  union all
  select dateadd(Day, 1, ReportDate)
  from dates
  where ReportDate < '2029-12-31'
)
select *
from Dates
option (maxrecursion 32767)

That works just fine! But if we can do it with dates, we can do it with numbers as well:

with numbers as (
  select 1 as number
  union all
  select number + 1
  from numbers
  where number < 10000
)
select *
from numbers
option (maxrecursion 32767)

Do note that in both cases I used the maxrecursion option. By default (without the option) the Recursive Statement is executed 100 times. Since in both the Dates and Numbers cases we need to go beyond that, you will get an error becuase the Recursive part cannot be completed.

By setting the maxrecursion option to its maximum of 32767, the Recursive part can do its job as planned.

Advanced Pattern Recognition

For this case I needed to validate a mobile phone number because it needed to be interfaced with another application. The source application had no restrictions on the mobile phone number other than its length. The receiving application however needed a well formated number.

So a Dutch mobile phone number should start with 06. That is the easy part. But then a dash (-) or a space ( ) was allowed. Or just keep typing without a space. After that, there should be 8 numbers.

So Patindex to the rescue!

If you are not yet familiar with Patindex. This function will tell you if a given pattern is present in a string. By giving the position of its first occurance in that string. So if you would look for 06 in a mobile phone number, it should be at position 1 or it would not be a valid number.

With that said, have a look at this one:

declare @Mobile nvarchar(15) = '0612345678'
select @Mobile,
case
  when len(@Mobile) = 11
  then patindex('[0][6][- ][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9]%', @Mobile)
  when len(@Mobile) = 10
  then patindex('[0][6][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9]%', @Mobile)
  else 0
end as Valid

This always seems a bit tricky so let's break this one down.

The first position should be a 0 so that's the [0]. Then we should have a 6 so that is the [6]. Now since all the numbers should start with 06, why not use [06]?

Well, as you can see for the third position I used [- ]. That means the third position shoud either be a dash or a space. So between the square brackets is 1 position only. So using [06] at the begining means the first position could be either a 0 or a 6.

For the remaining 8 positions it can be any number from 0 to 9. So you could use [0123456789] but [0-9] is the lazy way to do that.

True, such Patindex expressions can be quite long and hard to read at first glance. But they are really powerful for such complex validations with lots of wildcards.

'Abusing' the Cross Apply

Sometimes you have a Case Statement in your Select Statement. For example something like this one:

select h.FullName, h.mv1,
case
  when h.mv1 = 'M' then 'Male'
  when h.mv1 = 'V' then 'Female'
  else 'Unknown'
end as Gender
from [602].dbo.humres h
where h.mv1 is not null
--and Gender = 'Male'

Now even when I give the Case an alias Gender, I cannot use it as a Constraint in the Where Statement. Just try that by removing the dashes from the last line. The Column does not exist.

Yeah, right. In this example I could use h.mv1 = 'M' in the Where Statement. But that's because I kept the Case simple for better understanding. In many cases it is not that simple.

One way that I see quite often used is just to copy the Case and use it in the Where Statement also. And although this works, it is not the best way to do that. When you have to modify the Case later, chances are that you do change it in the Select part but forget to modify it in the Where as well.

Ah, if only there was a better way to do this!

Actually there is. By using a Cross Apply which is originally not intended for this. A Cross Apply is much like an Inner Join and can be used as such. The difference is that a Cross Apply works better if you want to join on multiple Columns. If there is only one Column needed for the Join? Just go for the regular Join.

Anyway, let's see how we can abuse the Cross Apply in this scenario:

select h.FullName, h.mv1,
x.Gender
from [602].dbo.humres h
cross apply(
  values (
  case
    when h.mv1 = 'M' then 'Male'
    when h.mv1 = 'V' then 'Female'
    else 'Unknown'
  end
  )
) as x(Gender)
where h.mv1 is not null
and x.Gender = 'Male'

Instead of using the Cross Apply to go to another Table, I just put in a value. For that value I used the Case as before. So basically that results in a Table with only 1 Row and 1 Column.

Then I aliased the Cross Apply Table as x. And since the values do not provide a Column Name, I specified it right there with the Alias.

Now that may seem a little bit weird. A Table with just 1 Row and 1 Column. But look at the second and the last line.

In both places I can now use x.Gender!

This means no code duplication. So when the Case needs to be modified later, it is impossible to forget to modify it in another place as well. It is only in one place and one place only! And it even has less lines than copying it in the Where Statement.

That's it for today!

Yes, those were my 4 SQL tips and tricks for today. If you enjoyed them, let me know and as I am already thinking about the next ones. Do you have suggestions for that? Those are welcome too!

You can leave a comment or send a question to andre@andrespeek.com. Let me know if you got inspired and stay safe and healthy always!