title: IF NOT EXISTS in Merge statement?
body: <p>I want to do the following, when primary keys matched and if there are no rows with active 'Y' insert records. Is this possible?</p>
  
  <p>I tried this:</p>
  
  <pre><code>-- Merge statement
  MERGE INTO table1 AS DST
  USING table2 AS SRC
  ON (SRC.Code = DST.Code)
  
   --Existing records updated if data changes
  WHEN MATCHED 
  AND IF NOT EXISTS (WHERE active='Y' FROM table1 )
  
  THEN
  INSERT INTO table1 (colum)
  SELECT value
  
  +-------+-------------+--------+
  | Code  | description | Active |
  +-------+-------------+--------+
  | AB    | just        |    |
  |       |  something  | No     |
  +-------+-------------+--------+
  </code></pre>
  
  <p>only when there is no active record with the same Code, I want to insert a record. The new record would look like this</p>
  
  <pre><code>+-------+-------------+--------+
  | Code  | description | Active |
  +-------+-------------+--------+
  | AB    | something   |    |
  |       | else        | YES    |
  +-------+-------------+--------+
  </code></pre>
  
  <p>I hope that makes it more clear</p>
  
  <p>edit: Never mind its not possible, I just got this error message:
  An action of type 'INSERT' is not allowed in the 'WHEN MATCHED' clause of a MERGE statement.</p>