title: How to update (merge) when similar item exists in the table
body: <p>(Edit: In case anyone would like to try, this is the SQL Fiddle URL: <a href="http://sqlfiddle.com/#!9/39c72c/1" rel="nofollow noreferrer">http://sqlfiddle.com/#!9/39c72c/1</a>)</p>
  
  <p>Suppose I have table <code>A</code> which looks like below:</p>
  
  <pre><code>col_1    col_2    cnt_1    mapped_val
    A1       B1        10     mapped_A1
    A2       B2         5     mapped_A2
    A3       B3         0     mapped_A3
  ....
  </code></pre>
  
  <p>I also have table <code>B</code> which looks like below:</p>
  
  <pre><code>col_1    col_2    cnt_1    mapped_val
    A1       B1        20     update_to_this
    A2       B2         5     do_not_update_to_this
    D1       D2         5     mapped_D1
    E3       E3         0     mapped_E3
  ....
  </code></pre>
  
  <p>For each of the row in table <code>B</code>, I'd like to look it up in table <code>A</code> using <code>col_1</code> and <code>col_2</code>, AND if <code>cnt_1</code> of table <code>A</code> is smaller than that of table <code>B</code>, then I'd like to update <code>cnt_1</code> and <code>mapped_val</code> entries in table <code>A</code> with that of table <code>B</code>. For the ones from table <code>B</code> that does not match, I'd like to append them to table <code>A</code>. The result by applying such an operation to table <code>A</code> would be:</p>
  
  <pre><code>col_1    col_2    cnt_1    mapped_val
    A1       B1        20     update_to_this
    A2       B2         5     mapped_A2
    A3       B3         0     mapped_A3
    D1       D2         5     mapped_D1
    E3       E3         0     mapped_E3
  ....
  </code></pre>
  
  <p>I think <code>MERGE INTO</code> (please see my best try below) comes close to accomplishing this, but I'm not sure how to implement comparison logic as part of <code>MERGE INTO</code> query.</p>
  
  <pre><code>MERGE INTO 
      A AS a
  USING
      B AS b
  ON 
      a.col_1 = b.col_1
      AND
      a.col_2 = b.col_2
  WHEN MATCHED -- AND a.cnt_1 &lt; b.cnt_1?
      THEN UPDATE 
          SET 
              a.cnt_1 = b.cnt_1,
              a.mapped_val = b.mapped_val
  WHEN NOT MATCHED -- AND a.cnt_1 &lt; b.cnt_1?
      THEN INSERT
          (col_1, col_2, cnt_1, mapped_val)
      VALUES
          (b.col_1, b.col_2, b.cnt_1, b.mapped_val);
  </code></pre>
  
  <p>Thank you in advance for your answers/suggestions!</p>