title: How do I generate a uniqueidentifier for a set of records with a distinct key?
body: <p>I have a record set that looks like this:</p>
  
  <pre class="lang-none prettyprint-override"><code>| key_sk | unique_id                            |
  |--------|--------------------------------------|
  | 2      | null                                 |
  | 2      | null                                 |
  | 3      | 83a1c90b-e58d-4db4-b438-a79edfb28e60 |
  | 3      | 83a1c90b-e58d-4db4-b438-a79edfb28e60 |
  | 4      | 4ce66783-0b84-4e8a-a0de-c3284e4d9cd0 |
  | 5      | null                                 |
  </code></pre>
  
  <p>I would like to generate a unique id for each unique <code>key_sk</code> set where the <code>unique_id</code> is null. For the above I'd like <code>key_sk 2</code> to have a a single <code>unique_id</code> like <code>key_sk 3</code> does.</p>
  
  <p>My attempt below generated a different <code>uniqueidentifier</code> for each set. I think this is because of the recursive nature of common table expressions: each join to the CTE causes <code>NEWID()</code> to be called.</p>
  
  <pre class="lang-sql prettyprint-override"><code>;with update_id_cte as
  (
    select distinct hr.key_sk
          ,NEWID() as gened_unique_id
      from history_record hr
     where hr.unique_id is null
  )
  update hr
     set hr.unique_id = cte.gened_unique_id
    from history_record hr
         join update_id_cte cte
           on hr.key_sk = cte.key_sk
  </code></pre>
  
  <p>There's probably a simpler way than utilizing a CTE to do this. How can I generate and update the <code>history_record</code> table with a single <code>uniqueidentifier</code> for each distinct <code>key_sk</code>?</p>