title: How to update both the source and the target using MERGE?
body: <p>I have two tables that I want to merge. Each has row contains the last time it was modified. I want to merge the tables such that the newest value is assigned to both tables.</p>
  
  <p>This is the code I want to use, what's commented out is what's causing my problems.</p>
  
  <pre><code>-- synchronize databases
  
  MERGE [ClientDB].[dbo].[Table] trgt
  USING [MasterDB].[dbo].[Table] src
  
  ON trgt.ID = src.ID
  
  WHEN MATCHED THEN
    -- IF src.LastModified &gt; trgt.LastModified   -- if the source is newer 
         UPDATE SET trgt.[Info] = src.[Info]     -- update the target
    -- ELSE 
      -- UPDATE SET src.[Info] = trgt.[Info]     -- otherwise update the source
  
  WHEN NOT MATCHED BY SOURCE
      THEN DELETE
  
  WHEN NOT MATCHED BY TARGET
      THEN INSERT ([Info]) VALUES (src.[Info]);
  </code></pre>
  
  <p>It doesn't work for two reasons,</p>
  
  <p>1) it doesn't appear to syntactically like the <code>IF</code> statement nested inside the <code>WHEN MATCHED THEN</code>. </p>
  
  <p>2) It also doesn't want me to update the source, which is problematic because I want to synchronize both ways. If I switch <code>trgt</code> and <code>src</code> I get the error:</p>
  
  <blockquote>
    <p>The multi-part identifier "src.Description" could not be bound.</p>
  </blockquote>
  
  <p>How can I accomplish this? Should I abandon <code>MERGE</code> or does it have the capability to do what I want? </p>
  
  <p>I'm extremely new to SQL so feel free to correct my thinking if it's wrong anywhere. Thanks in advance.</p>