title: UPDATE to assign serial numbers per group
body: <p>Table <code>users</code>:</p>
  <div class="s-table-container">
  <table class="s-table">
  <thead>
  <tr>
  <th>id</th>
  <th>name</th>
  <th>group_id</th>
  <th>column to update</th>
  </tr>
  </thead>
  <tbody>
  <tr>
  <td>1</td>
  <td>Paul</td>
  <td>1</td>
  <td></td>
  </tr>
  <tr>
  <td>2</td>
  <td>Mike</td>
  <td>1</td>
  <td></td>
  </tr>
  <tr>
  <td>3</td>
  <td>Charlie</td>
  <td>1</td>
  <td></td>
  </tr>
  <tr>
  <td>4</td>
  <td>Elza</td>
  <td>2</td>
  <td></td>
  </tr>
  </tbody>
  </table>
  </div>
  <p>Table <code>groups</code>:</p>
  <div class="s-table-container">
  <table class="s-table">
  <thead>
  <tr>
  <th>id</th>
  <th>name</th>
  </tr>
  </thead>
  <tbody>
  <tr>
  <td>1</td>
  <td>coolest group</td>
  </tr>
  <tr>
  <td>2</td>
  <td>random group</td>
  </tr>
  </tbody>
  </table>
  </div>
  <p>Table <code>users</code> after update:</p>
  <div class="s-table-container">
  <table class="s-table">
  <thead>
  <tr>
  <th>id</th>
  <th>name</th>
  <th>group_id</th>
  <th>column to update</th>
  </tr>
  </thead>
  <tbody>
  <tr>
  <td>1</td>
  <td>Paul</td>
  <td>1</td>
  <td>3</td>
  </tr>
  <tr>
  <td>2</td>
  <td>Mike</td>
  <td>1</td>
  <td>2</td>
  </tr>
  <tr>
  <td>3</td>
  <td>Charlie</td>
  <td>1</td>
  <td>1</td>
  </tr>
  <tr>
  <td>4</td>
  <td>Elza</td>
  <td>2</td>
  <td>1</td>
  </tr>
  </tbody>
  </table>
  </div>
  <p>Group 1 has 3 users, we order them by name, and assign each an increment.
  Group 2 has 1 user, we assign only one increment.</p>
  <p>I'm trying to update users, per group, with increments on a specific column according to their name order.</p>
  <p>So far I tried:</p>
  <pre><code>UPDATE users u
  SET columntoupdate = g.increment
  FROM (
           SELECT ROW_NUMBER() OVER (ORDER BY name) AS increment
           FROM users u2 INNER JOIN groups g2 ON g2.id = u2.group_id
           WHERE u.group_id = g2.id
       ) g
  </code></pre>
  <p>But <code>u.group_id = g2.id</code> gives me an error about not being able to reference it in the subquery.</p>
  <p><a href="https://dbfiddle.uk/?rdbms=postgres_13&amp;fiddle=8b17608b6b71241c83266334edfaf52d" rel="nofollow noreferrer">Fiddle showing the problem</a></p>