title: CTE Issues for Selecting Distinct Values and assigned NEWID() to those values
body: <p>I'm having an issue with some CTE code I'm working with.  My current goal is that I have a table that has a 'Type' column.  I want to select all the DISTINCT types from that 'Type' column and, for each type, assign a value of NEWID() to a separate column.</p>
  <p>Heres a sample table that im starting with:</p>
  <div class="s-table-container">
  <table class="s-table">
  <thead>
  <tr>
  <th>Type</th>
  <th>NEW ID</th>
  </tr>
  </thead>
  <tbody>
  <tr>
  <td>1</td>
  <td>NULL</td>
  </tr>
  <tr>
  <td>1</td>
  <td>NULL</td>
  </tr>
  <tr>
  <td>4</td>
  <td>NULL</td>
  </tr>
  <tr>
  <td>4</td>
  <td>NULL</td>
  </tr>
  <tr>
  <td>4</td>
  <td>NULL</td>
  </tr>
  <tr>
  <td>MA</td>
  <td>NULL</td>
  </tr>
  <tr>
  <td>MA</td>
  <td>NULL</td>
  </tr>
  </tbody>
  </table>
  </div>
  <pre><code>    WITH unique_gen_id AS (
          SELECT DISTINCT type, NEWID() AS unique_id
          FROM tmp
          )
  
      UPDATE t
      SET t.unique_id = u.unique_id
      FROM tmp t  INNER JOIN
      unique_gen_id u ON t.type = u.type
  </code></pre>
  <p>This query almost works-- it assigns a &quot;NEWID()&quot; unique value to each respective &quot;Type&quot; with a few mishaps.  (Type is not specific to either an int or character, could be anything).</p>
  <div class="s-table-container">
  <table class="s-table">
  <thead>
  <tr>
  <th>Type</th>
  <th>NEW ID</th>
  </tr>
  </thead>
  <tbody>
  <tr>
  <td>1</td>
  <td>B280347A-C394-4656</td>
  </tr>
  <tr>
  <td>1</td>
  <td>B280347A-C394-4656</td>
  </tr>
  <tr>
  <td>4</td>
  <td>C03F0E24-7187-4CC2</td>
  </tr>
  <tr>
  <td>4</td>
  <td>D10415A8-55BD-4251</td>
  </tr>
  <tr>
  <td>4</td>
  <td>D10415A8-55BD-4251</td>
  </tr>
  <tr>
  <td>MA</td>
  <td>DBE92CA0-B440-484D</td>
  </tr>
  <tr>
  <td>MA</td>
  <td>DBE92CA0-B440-484D</td>
  </tr>
  </tbody>
  </table>
  </div>
  <p>As you can see, the query returned almost fine. It failed, however, with &quot;Type&quot; of '4' as it assigned 2 separate 'NEWIDS()' when its supposed to match all the way through.</p>
  <p>It gets worse with different data-- I tried on different data using different 'Types' (For example, I had 100 records with Type of &quot;1&quot; that returned 100 unique IDs for each record when its supposed to be 1 NEWID() for all of Type &quot;1&quot;, then a new NEWID() for a different type etc etc) and it was catastrophic.</p>