title: Can I iteratively update a single column multiple times - in a single statement?
body: <p>I have a table that I receive as a table-type parameter in my stored procedure (it comes from an excel workbook source, but that's a different story). It has several columns that I need to validate the values against a list of valid values for each column. </p>
  
  <p>Let's say my table <code>OriginDetails</code> looks like this (please note that this is just mock data;  I have two such tables with each 8 columns I will be validating) - </p>
  
  <pre><code>Origin   | Status     | Priority | ErrMsg
  ------------------------------------------
  Testing  | In Review  | Low      | 
  Design   | Initiated  | Medium   | 
  Prod     | Declined   | Critical |           
  </code></pre>
  
  <p>And, I am validating the values in the columns <code>Origin</code>, <code>Status</code> and <code>Priority</code> against three different lists (actually I am validating the values against data in tables, but for simplicity I hard-coded these values here), and updating the <code>ErrMsg</code> column based on my validations - </p>
  
  <pre><code>UPDATE OriginDetails 
  SET ErrMsg = ErrMsg + '|Invalid Origin' 
  WHERE Origin NOT IN ('Pre-Design','Design','Development') 
  
  UPDATE OriginDetails 
  SET ErrMsg = ErrMsg + '|Unrecognized Status' 
  WHERE Status NOT IN ('In Review','Approved') 
  
  UPDATE OriginDetails 
  SET ErrMsg = ErrMsg + '|Priority check failed' 
  WHERE Priority NOT IN ('Critical','Medium','High')
  </code></pre>
  
  <p>This is all fine and dandy, works great - but I end up with 16 such update statements for 2 tables together, so I have a really large and ugly block of code (and a lot of duplication also since I have almost identical code for 2 tables).</p>
  
  <p>Is there a way I can actually do all the updates in one single statement for each table? </p>
  
  <p>Something like the below, <strong>except that it should execute each of the conditions instead of only one</strong> - </p>
  
  <pre><code>UPDATE OriginDetails
  SET ErrMsg = ErrMsg + 
  (CASE WHEN Origin NOT IN ('Pre-Design','Design','Development') 
             THEN '|Invalid Origin'
        WHEN Status NOT IN ('In Review','Approved') 
             THEN '|Unrecognized Status'
        WHEN Priority NOT IN ('Critical','Medium','High') 
             THEN '|Priority check failed'
  END)
  </code></pre>
  
  <p>Any ideas/direction are appreciated. Thanks.</p>