title: SQL Merge Error : The MERGE statement attempted to UPDATE or DELETE
body: <p>I have a source table</p>
  
  <pre><code>select 54371 Id, 'foo' [CreateBy], '2016-10-24 09:29:18.548'[CreateDate], 'foo'[UpdateBy],  '2016-10-24 09:29:18.548'[UpdateDate], 'E'[MT], 185761[MID], 3[BGID]
  union
  select 54372, 'foo', '2016-10-24 09:30:18.548', 'foo',  '2016-10-24 09:30:18.548',  'E', 185761, 2
  </code></pre>
  
  <p>and a target table</p>
  
  <pre><code>select 54379 Id, 'foo' [CreateBy], '2016-10-24 09:29:18.548'[CreateDate], 'foo'[UpdateBy],  '2016-10-24 10:29:18.548'[UpdateDate], 'E'[MT], 185761[MID], 3[BGID]
  </code></pre>
  
  <p>What I want is to match based on MT, MID and</p>
  
  <ol>
  <li>Insert if not exists</li>
  <li>update if BGID matches</li>
  <li>delete if BGID does not matches</li>
  </ol>
  
  <p>When I use SQL Merge Statement I get error</p>
  
  <blockquote>
    <p>The MERGE statement attempted to UPDATE or DELETE the same row more than once. This happens when a target row matches more than one source row. A MERGE statement cannot UPDATE/DELETE the same row of the target table multiple times. Refine the ON clause to ensure a target row matches at most one source row, or use the GROUP BY clause to group the source rows.`</p>
  </blockquote>
  
  <p>My Merge is like this</p>
  
  <pre><code>MERGE   
    FooBar AS target
  USING
  (
  SELECT
  E.[Id],
  E.[CreateBy],
  E.[CreateDate],
  E.[UpdateBy],
  E.[UpdateDate],
  E.[MT],
  E.[MID],
  E.[BGID]
  FROM @FooBar E
  ) AS source
  ON
  source.MID = target.MID
  AND source.MT = target.MT
  WHEN MATCHED and target.[BGID] = source.[BGID] THEN
  UPDATE SET
  target.[UpdateBy] = Source.[UpdateBy]
  ,target.[UpdateDate] = Source.[UpdateDate]
  When Matched and source.BGID &lt;&gt; target.BGID THEN
  DELETE
  WHEN NOT MATCHED THEN
  INSERT([CreateBy]
  ,[CreateDate]
  ,[UpdateBy]
  ,[UpdateDate]
  ,[MT]
  ,[MID]
  ,[BGID])
  VALUES
  (
  Source.[CreateBy]
  ,Source.[CreateDate]
  ,Source.[UpdateBy]
  ,Source.[UpdateDate]
  ,Source.[MT]
  ,Source.[MID]
  ,Source.[BGID]
  );
  </code></pre>
  
  <p>What am I missing?</p>