title: Iteratively get the max of a data frame column, add one and repeat for all rows in spark/sql
body: <p>I'm using spark and mainly interacting with it via sql (I'm a novice to both, so please bear with me).  I have a data set that looks like this in a table called <code>taxonomies</code>:</p>
  <pre><code>level_4         level_5    level_6    level_7       system_id   node_id
  American League West       null       null           4          633
  American League East       null       null           4          634
  National League West       Dodgers    bellinger      3          635
  National League Central    Cardinals  null           null       null
  American League Central    null       null           null       null
  </code></pre>
  <p>I'm going to get the system_id columns by left joining another table; what I want to do here is get a new node_id for the null values by adding one to the max of the node_id column.</p>
  <p>I adapted this query from an answer to a previous <a href="https://stackoverflow.com/a/68169700/9492720">question</a>:</p>
  <pre><code>WITH cte AS (
      SELECT max(node_id) AS maxid FROM taxonomies
    )
    SELECT cte.maxid + row_number() OVER (ORDER BY node_id) AS node_id, system_id, `level_4`, `level_5`, `level_6`, `level_7`
    FROM taxonomies
      LEFT JOIN cte WHERE taxonomies.node_id IS null
    UNION
    SELECT * FROM taxonomies WHERE node_id IS NOT null
  </code></pre>
  <p>What I don't understand is that this appropriately makes new node_id for the null values, but for everything else it swaps system_id and node_id.  I've tried switching node_id and system_id after AS, but all this does is change the column names with the same results.  What I want is this:</p>
  <pre><code>level_4         level_5    level_6    level_7       system_id   node_id
  American League West       null       null           4          633
  American League East       null       null           4          634
  National League West       Dodgers    bellinger      3          635
  National League Central    Cardinals  null           null       636
  American League Central    null       null           null       637
  </code></pre>
  <p>Any help is greatly appreciated!</p>