title: Update a SQL Server table from a CTE and Case statement
body: <p>I have this code:</p>
  
  <pre><code>WITH CTE AS
  (
      SELECT 
          LTRIM(RTRIM([EMP_ID])) AS empID
      FROM 
          [SAMPLE].[dbo].[segments]
  )
  SELECT
      (CASE WHEN LEN(LTRIM(RTRIM(EMPID))) = 5 
               THEN RIGHT(('100' + EMPID), 8)
            WHEN LEN(LTRIM(RTRIM(EMPID))) = 6 
               THEN RIGHT(('10' + EMPID), 8)
            WHEN LEN(EMPID) = 7 
               THEN RIGHT('10' + (LEFT(LTRIM(EMPID), 6)), 8)
            ELSE empID
       END) EMP_ID
  FROM CTE
  </code></pre>
  
  <p>What I want to do is use the above state to update my <code>emp_id</code> column to the new 8 digit ids. Running the script returns the IDs correctly now I just need to update the table.</p>
  
  <p>I tried the following and all it did was made all the IDs the same </p>
  
  <pre><code>WITH CTE AS 
  (
      SELECT 
          LTRIM(RTRIM([EMP_ID])) AS empID
      FROM 
          [SAMPLE].[dbo].[segments]
  )
  UPDATE segments
  SET EMP_ID = (case when len(ltrim(rtrim(EMPID))) = 5 then right(('100' + EMPID),8 )
          when len(ltrim(rtrim(EMPID))) = 6 then right(('10' + EMPID),8 )
          when len(EMPID) = 7 then right('10'+(left(ltrim(EMPID),6)),8)
        else empID
        end) 
  FROM CTE
  </code></pre>
  
  <p>What is the correct <code>UPDATE</code> statement to use for this?</p>