TSQL MERGE with multiple sources

Question

Originally i tried using an IF/ELSE to accomplish an "UPSERT" and someone suggested I use MERGE my issue with MERGE is it does not look like I can use two sources.

Here is my Original attempt at an upsert:

IF ((SELECT COUNT(CAST(StudentuserID AS int)) FROM HL_StudentAttendance WHERE StudentUserID=1)>0)
UPDATE HL_StudentAttendance
SET
CID = CAST('[querystring:CID]' AS int),
CalendarEventID = CAST('[querystring:CEID]' AS int),
StudentUserID = CAST('[StudentUserID]' AS int),
Attendance = '[Attendance]'
ELSE
INSERT INTO HL_StudentAttendance
(CID,CalendarEventID,StudentUserID,Attendance)
VALUES
(CAST('[querystring:CID]' AS int), CAST('[querystring:CEID]' AS int), CAST('[StudentsUserID]' AS int),'[Attendance]')

Even though the IF statement result is 8 so 8>0 it should run my update it's always running an insert not sure if my if/else logic is missing something.

Here is an attempt with MERGE

MERGE 
   HL_StudentAttendance AS target
USING 
   HL_CourseRegistrations AS source
ON 
   target.StudentUserID = source.UserID
   AND source.
WHEN MATCHED THEN 
   UPDATE SET 
   Attendance = '[Attendance]'
WHEN NOT MATCHED THEN 
   INSERT (CID, CalendarEventID, StudentUserID, Attendance) VALUES ('[querystring:CID]', '[querystring:CEID]', '[UserID]', '[Attendance]')
; 

My issue here is that I have data in my table HL_CourseEvents that should be used to grab an additional source item being CalendarEventID otherwise the MERGE works but inserts duplicate entries into my HL_StudentAttendance as there is no parameter for CalendarEventID

Any suggestions appreciated.

UPDATE

The following query does work, except that it's inserting new records if anything is changed on the attendance field rather than update properly. I suspect because the WHERE clause on my update is not there. I tried adding it getting execution errors

MERGE HL_StudentAttendance
USING
(
SELECT cr.CID, ce.CalendarEventID, cr.UserID FROM HL_CourseRegistrations cr
JOIN HL_CalendarEvents ce
ON ce.CID = cr.CID
) tmpTable
ON
HL_StudentAttendance.StudentUserID = tmpTable.UserID
AND HL_StudentAttendance.CalendarEventID = tmpTable.CalendarEventID
WHEN MATCHED THEN
UPDATE
SET
Attendance = 'Attended Late'
WHEN NOT MATCHED THEN 
INSERT (CID,CalendarEventID,StudentUserID,Attendance) VALUES ('1','1','1','555')
;

Results of the query run twice. If i keep running it, it will always insert more instead of updating:

ID  CID CalendarEventID StudentUserID Attendance
1   1   1               1             Attended Late
2   1   1               1             Attended Late
3   1   1               1             Attended Late
4   1   1               1             Attended Late
5   1   1               1             Attended Late
6   1   1               1             555
7   1   1               1             555
8   1   1               1             555
9   1   1               1             555

Answer 1

Based on additional information, try this below. I've hacked together a schema and some data for demonstration purposes only so you'll have to see how your schema compares. However, the two examples at the bottom do what you're asking I believe. The first inserts a new record if it does not exist based on the user id. The second marks that record as "Attended late" if it exists. Note that, for real life you're probably going to need multiple parameters as a user could be signed up for multiple courses, so you'll want to pass in the course or calendar event id as well. Let me know if this gets you started or you need something additional.

--
-------------------------------------------------
CREATE TABLE [test].[HL_StudentAttendance]
  (
       [StudentUserID]     INT
       , [CalendarEventID] INT
       , [Attendance]      NVARCHAR(250)
       , [CID]             INT
  );

CREATE TABLE [test].[HL_CourseRegistrations]
  (
       [CID]      INT
       , [UserID] INT
  );

CREATE TABLE [test].[HL_CalendarEvents]
  (
       [CalendarEventID] INT
       , [CID]           INT
  );

go

--
-------------------------------------------------
INSERT INTO [test].[HL_CourseRegistrations]
            ([CID]
             , [UserId])
VALUES      (1,1),
            (3,4),
            (4,5);

INSERT INTO [test].[HL_CalendarEvents]
            ([CalendarEventID]
             , [CID])
VALUES      (1,1);

go

--
-------------------------------------------------
CREATE PROCEDURE [test].[set_attendance] @user INT
AS
    BEGIN
        MERGE INTO [test].[HL_StudentAttendance] AS [target]
        USING (SELECT [cr].[CID]
                      , [ce].[CalendarEventID]
                      , [cr].[UserID]
               FROM   [test].[HL_CourseRegistrations] [cr]
                      JOIN [test].[HL_CalendarEvents] [ce]
                        ON [ce].[CID] = [cr].[CID]) AS [source]
        ON [target].[StudentUserID] = @user
           AND [target].[CalendarEventID] = [source].[CalendarEventID]
        WHEN MATCHED THEN
            UPDATE SET [Attendance] = 'Attended Late'
        WHEN NOT MATCHED BY target THEN
            INSERT ([CID]
                    , [CalendarEventID]
                    , [StudentUserID]
                    , [Attendance])
            VALUES ('1'
                    , '1'
                    , @user
                    , '555');
    END;

go

--
-- inserts a new record
-------------------------------------------------
EXECUTE [test].[set_attendance]
    @user=12;

SELECT *
FROM   [test].[HL_StudentAttendance];


--
-- marks an existing record as late
-------------------------------------------------
EXECUTE [test].[set_attendance]
    @user=12;

SELECT *
FROM   [test].[HL_StudentAttendance];