title: SQL Update with Case, either assign or do nothing
body: <p>Coming off the back of this question <a href="https://stackoverflow.com/questions/9798022/sql-update-same-query-different-results-each-time">here</a>, is there a way to put a <code>CASE</code> statement into an SQL <code>UPDATE</code> that either update's or does nothing. Currently i have the below: </p>
  
  <pre><code>UPDATE CustomerAddress
  SET IsPrimaryAddress = CASE WHEN c2.IsPrimaryAddress=1 THEN 1 ELSE 0 END
  FROM CustomerAddress
  join #AddressToDeleteMasterOfLesserId p1 on CustomerAddress.Id=p1.[Id that is master]
  join CustomerAddress c2 on p1.[Id to delete]=c2.Id 
  </code></pre>
  
  <p>The problem arises on this line:</p>
  
  <pre><code>SET IsPrimaryAddress = CASE WHEN c2.IsPrimaryAddress=1 THEN 1 ELSE **0** END
  </code></pre>
  
  <p>When I set it to 0 otherwise. The update should check the first condition, and if so, set to 1, but, if the condition is not met, I don't want to set to 0, I need to retain the original value.</p>
  
  <pre><code>SET IsPrimaryAddress = CASE WHEN c2.IsPrimaryAddress=1 THEN 1 ELSE *CustomerAddress.IsPrimary* END
  </code></pre>
  
  <p>Doesn't work however, because it gets the value from the <code>FROM</code> statement, rather then the value it has been currently set too. What i want is something like this</p>
  
  <pre><code>SET IsPrimaryAddress = CASE WHEN c2.IsPrimaryAddress=1 THEN 1 ELSE *Do nothing* END
  </code></pre>
  
  <p>But this is still an assignment, and tries to set to <code>NULL</code>.</p>
  
  <p>Is this above possible?</p>
  
  <p>UPDATE:</p>
  
  <p>Sample data:</p>
  
  <p>pair's of ID's that represent address as so</p>
  
  <pre><code>ID to keep  ID to delete
  ------------------------
  10          21
  10          22
  11          31
  12          41
  </code></pre>
  
  <p>I'll focus on what happens with 10. So 10 has <code>IsPrimaryAddress=0</code>, 21 has <code>IsPrimaryAddress=1</code>, and 22 has <code>IsPrimaryAddress=0</code>.</p>
  
  <p>What I believe happens is this (in pseudocode):</p>
  
  <p>Set IsPrimaryAddress = if 21.IsPrimaryAddress=1 then set to 21.IsPrimaryAddress else set to 10.IsPrimaryAddress</p>
  
  <p>So we have set 10.IsPrimaryAddress to 1. Now:</p>
  
  <p>Set IsPrimaryAddress = if 22.IsPrimaryAddress=1 then set to 22.IsPrimaryAddress else set to 10.IsPrimaryAddress</p>
  
  <p>Which i would hope 10.IsPrimaryAddress equals 1. But it appears to get the original value which was 0, and now the update has been lost. </p>