title: TSQL MERGE with multiple sources
body: <p>Originally i tried using an IF/ELSE to accomplish an "UPSERT" and someone suggested I use <code>MERGE</code> my issue with <code>MERGE</code> is it does not look like I can use two sources.</p>
  
  <p>Here is my Original attempt at an upsert:</p>
  
  <pre><code>IF ((SELECT COUNT(CAST(StudentuserID AS int)) FROM HL_StudentAttendance WHERE StudentUserID=1)&gt;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]')
  </code></pre>
  
  <p>Even though the IF statement result is <code>8</code> so <code>8&gt;0</code> it should run my update it's always running an insert not sure if my if/else logic is missing something.</p>
  
  <p>Here is an attempt with <code>MERGE</code></p>
  
  <pre><code>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]')
  ; 
  </code></pre>
  
  <p>My issue here is that I have data in my table <code>HL_CourseEvents</code> that should be used to grab an additional source item being <code>CalendarEventID</code> otherwise the <code>MERGE</code> works but inserts duplicate entries into my <code>HL_StudentAttendance</code> as there is no parameter for <code>CalendarEventID</code></p>
  
  <p>Any suggestions appreciated.</p>
  
  <p>UPDATE</p>
  
  <p>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</p>
  
  <pre><code>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')
  ;
  </code></pre>
  
  <p>Results of the query run twice. If i keep running it, it will always insert more instead of updating:</p>
  
  <pre><code>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
  </code></pre>