Question: I have a basic understanding of the [MERGE](http://technet.microsoft.com/en-us/library/bb510625.aspx) syntax for SQL Sever 2008 but have a question about an advanced use of the concept.

Imagine I have a sports team that has 30 players in it. Each player is a record in a table. Now, half way through the season, some players have left (injury, whatever) with some new blood arrived to replace them.

So I wish to update the database table to accurately reflect this change.

So when I use the `MERGE` syntax, if a new player doesn't exist, then it gets inserted:

```
WHEN NOT MATCHED THEN
    INSERT blah blah blah...
    VALUES (blah blah and more blah)..

```

and when matched, then do nothing (so I don't need to have any `WHEN MATCHED THEN` clause).

But what about those players who have left? Is there where the `WHEN NOT MATCHED BY SOURCE THEN <merge_matched>` comes in to play?

If so, how should I use it?

Answer: 
You can use merge like that but then I guess that your source table for the new team must hold the entire new team including those that has not been modified.
Here is a simplified sample.

The season starts out with this team

```
PlayerName           Position
-------------------- --------
Brett Favre          QB
Joe Montana          QB
John Elway           QB

```

Half way through the season the coach realizes that it is not optimal to have a team with three rather old QB's. Brett refuses to quit so Joe has to go. John gets to play RB and at the same time we need some youngsters to get things going.

```
PlayerName           Position
-------------------- --------
Brett Favre          QB
John Elway           RB
Jerry Rice           WR
Karl Mecklenburg     LB

```

The merge statement to merge `NewTeam` to `Team` would look like this.

```
merge Team as T
using NewTeam as S
on S.PlayerName = T.PlayerName
when matched then
  update set Position = S.Position
when not matched by target then
  insert (PlayerName, Position) values (S.PlayerName, S.Position)
when not matched by source then
  delete;  

```
Yes, `WHEN NOT MATCHED BY SOURCE THEN <merge_matched>` is the section where you can take care about the players that have left.

According to the [doc](http://technet.microsoft.com/en-us/library/bb510625.aspx), you can use an UPDATE (for example, set the `Active` atribute to `0`) or a DELETE instruction there. 

You can find out from the documentation that there can be two such sections. In that case one of them is defined with an additional condition and can only accept an UPDATE instruction:

```
WHEN NOT MATCHED BY SOURCE AND *condition* THEN
    UPDATE SET ...
```

The other must not use a condition and is reserved for a DELETE instruction.