How do I use JOINs in a MERGE INTO statement?

Question

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:

[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

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.

So here's what I started to write (even though I know this is not correct syntax):

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;

Hopefully that illustrates the problem I'm running into. I want to insert any new data from the USING subquery into the [months_members] table. And I also want to delete anything from the [months_members] table that is not present in the USING subquery -- but only if it corresponds to a month in the current year. If there is data not present in the USING subquery that corresponds to a different year, I do not want to delete that. I want to leave that historical data alone. So the DATEPART condition is the kicker.

So I'm inserting into one table, [months_members], but I also need it to be aware of the [months] table. Is there any way to accomplish this? Or will I have to concede and iterate through a number of SQL queries?

Answer 1

You can use a CTE in place of the target table (or a view). SQL Server can trace the updates through the CTE/view. Here is a hacked-together demo:

SELECT *
INTO #temp
FROM sys.objects
GO

;WITH cte AS (
    SELECT t1.*
    FROM #temp t1
    JOIN #temp t2 ON t1.object_id = t2.object_id
)
MERGE cte 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 cte.object_id = u.[month_id]
WHEN MATCHED THEN DELETE;