title: SQL Server: Update where exists and Insert when not exists
body: <p>I have 2 tables, one is called
  <code>INTER</code> and the other one is called <code>COM</code>
  The relationship between them is 1:many
  Here's the structure of both tables</p>
  <p><code>INTER</code></p>
  <pre><code>NUMBER
  ADDRESS
  </code></pre>
  <p><code>COM</code></p>
  <pre><code>NUMBER
  CONTACT
  REFERENCE
  </code></pre>
  <p>To give some context about the tables:</p>
  <p>The <code>ADDRESS</code> field from the <code>INTER</code> table can be <em>empty</em> (empty string '')</p>
  <p>The <code>CONTACT</code> field of the <code>COM</code> table can be of two types: <em>&quot;E-mail&quot; or &quot;Website&quot;</em></p>
  <p>What I'm trying to accomplish is the following:</p>
  <p>I want to look at all the records from <code>COM</code> where <code>CONTACT</code>=<em>'Website'</em> and insert into <code>COM</code> the values of <code>(INTER.NUMBER,'Website',INTER.ADDRESS)</code>
  where <code>INTER.ADDRESS</code>&lt;&gt;'' and <code>INTER.NUMBER</code> <em>does not exist</em> in <code>COM.NUMBER</code></p>
  <p>Otherwise, I want to update the value of <code>COM.REFERENCE</code>, set it to <code>INTER.ADDRESS</code> where <code>COM.CONTACT</code>=<em>'Website'</em> and <code>INTER.NUMBER</code> exists in <code>COM.NUMBER</code> and <code>INTER.ADDRESS</code>&lt;&gt;''</p>
  <p>How can I accomplish this? I know what the merge statement is but according to the documentation merge does not work well on filtered queries. Also, I know about the exists statement but I don't know how to make this query work.</p>
  <p>In case you're wondering, unfortunately, the structure of these tables cannot be modified.</p>
  <p>Some sample data:</p>
  <p><code>INTER</code></p>
  <pre><code>ABCD123,google.com
  XUEH342,facebook.com
  IISI521,twitter.com
  IEIEK885,''
  </code></pre>
  <p><code>COM</code></p>
  <pre><code>ABCD123, Website, test.com
  ABCD123,E-mail,bob@gmail.com
  XUEH342,Website,facebook.com
  XASE456,Website, stackoverflow.com
  XASE456,E-mail,tom@gmail.com
  </code></pre>
  <p>After running the query, the results on COM table would look as the following:</p>
  <pre><code>ABCD123,Website, google.com
  ABCD123,E-mail,bob@gmail.com
  XUEH342,Website, facebook.com
  IISI521,Website, twitter.com
  XASE456,Website, stackoverflow.com
  XASE456,E-mail,tom@gmail.com
  </code></pre>
  <p>As you can see, IISI521,twitter.com got inserted,ABCD123,test.com got updated and IEIEK885,'' didn't get inserted because is an empty string.</p>