title: How to create INSERT query that adds sequence number in one table to another
body: <p>I have a table <code>sample_1</code> in a <strong>Postgres 10.7</strong> database with some longitudinal research data and an ascending <code>sequence</code> number per <code>key</code>. I need to <code>INSERT</code> data from a staging table (<code>sample_2</code>) maintaining the <code>sequence</code> column accordingly.</p>
  
  <p><code>sequence</code> numbers are 0-based. I assume I need a query to seek the greatest <code>sequence</code> number per <code>key</code> in <code>sample_1</code> and add that to each new row's follow-up sequence number. I'm mainly struggling at this step with the sequence number arithmetic. Tried this:</p>
  
  <pre><code>INSERT INTO sample_1 (KEY, SEQUENCE, DATA)
  SELECT KEY, sample_2.SEQUENCE + max(sample_1.SEQUENCE), DATA
  FROM sample_2;
  </code></pre>
  
  <p>However, I get errors saying I can't use 'sample_1.SEQUENCE' in Line 2 because that's the table being inserted in to. I can't figure out how to do the arithmetic with my insert sequence!</p>
  
  <p>Sample data:</p>
  
  <pre><code>sample_1
  
  | KEY         | SEQUENCE | DATA |
  +-------------+----------+------+
  | YMH_0001_XX |     0    |  a   |
  | YMH_0001_XX |     1    |  b   |
  | YMH_0002_YY |     0    |  c   |
  </code></pre>
  
  <pre><code>sample_2
  
  | KEY         | SEQUENCE | DATA |
  +-------------+----------+------+
  | YMH_0001_XX |     1    |  d   |
  | YMH_0002_YY |     1    |  e   |
  | YMH_0002_YY |     2    |  f   |
  </code></pre>
  
  <p>I want to continue ascending <code>sequence</code> numbers per <code>key</code> for inserted rows.</p>
  
  <p>To be clear, the resultant table in this example would be 3 columns and 6 rows as such:</p>
  
  <pre><code>sample_1
  
  | KEY         | SEQUENCE | DATA |
  +-------------+----------+------+
  | YMH_0001_XX |     0    |  a   |
  | YMH_0001_XX |     1    |  b   |
  | YMH_0001_XX |     2    |  d   |
  | YMH_0002_YY |     0    |  c   |
  | YMH_0002_YY |     1    |  e   |
  | YMH_0002_YY |     2    |  f   |
  </code></pre>