title: How do I use JOINs in a MERGE INTO statement?
body: <p>I have three tables I'm dealing with for a signup form: [months], [members], and [months_members]. That last one is simply a join table. They look something like this:</p>
  
  <pre><code>[months]
  ========
  [id] INT (Primary Key)
  [name] VARCHAR(50)
  [date] DATE
  
  [members]
  =========
  [id] INT (Primary Key)
  [fname] VARCHAR(50)
  [lname] VARCHAR(50)
  [email] VARCHAR(300)
  
  [months_members]
  ================
  [id] INT (Primary Key)
  [month_id] INT
  [member_id] INT
  </code></pre>
  
  <p>So, pretty simple, conceptually. There's a set list of members, and they can sign up for certain months. The form that I have, which allows them to sign up for different months, actually lets administrators enter (or change!) all of the signups all at once. And so I was hoping to use a MERGE statement to get the data into the database with a single query, rather than iterating through multiple queries.</p>
  
  <p>So here's what I started to write (even though I know this is not correct syntax):</p>
  
  <pre><code>MERGE INTO [months_members] mm
  INNER JOIN [months] mo ON mo.[id] = mm.[month_id] 
  USING (
      SELECT 1 AS [month_id], 1 AS [member_id] UNION ALL
      SELECT 2 AS [month_id], 3 AS [member_id] UNION ALL
      SELECT 4 AS [month_id], 4 AS [member_id]
  ) AS u ON mm.[month_id] = u.[month_id] AND mm.[member_id] = u.[member_id] 
  WHEN NOT MATCHED THEN 
      INSERT ([month_id], [member_id]) VALUES (u.[month_id], u.[member_id]) 
  WHEN NOT MATCHED BY SOURCE AND DATEPART(YEAR, mo.[start]) = 2013 THEN 
      DELETE;
  </code></pre>
  
  <p>Hopefully that illustrates the problem I'm running into. I want to insert any new data from the <code>USING</code> subquery into the <code>[months_members]</code> table. And I also want to delete anything from the <code>[months_members]</code> table that is <em>not</em> present in the <code>USING</code> subquery -- but <em>only if</em> it corresponds to a month in the current year. If there is data not present in the <code>USING</code> subquery that corresponds to a different year, I do <em>not</em> want to delete that. I want to leave that historical data alone. So the <code>DATEPART</code> condition is the kicker.</p>
  
  <p>So I'm inserting into one table, <code>[months_members]</code>, but I also need it to be aware of the <code>[months]</code> table. Is there any way to accomplish this? Or will I have to concede and iterate through a number of SQL queries?</p>