title: PostgreSQL - insert rows based on select from another table, and update an FK in that table with the newly inserted rows
body: <p>I am doing a data migration between two tables (splitting out a related table). The existing table is <code>reminders</code>, and it has a <code>start</code> column and a newly-added <code>dateset_id</code> column pointing to a new <code>dateset</code> table, which also has a <code>start</code> column. For every row in <code>reminders</code>, I want to <code>INSERT</code> a new row in <code>dateset</code> with the <code>start</code> value copied over, and <code>UPDATE</code> the corresponding row in <code>reminders</code> with the newly-inserted <code>dateset</code> ID.</p>
  
  <p>Here's the SQL I tried:</p>
  
  <pre><code>WITH inserted_datesets AS (
    INSERT INTO dateset (start)
    SELECT start FROM reminder
    RETURNING reminder.id AS reminder_id, id AS dateset_id
  )
  UPDATE reminder
  SET dateset_id = ids.dateset_id
  FROM inserted_datesets AS ids
  WHERE reminder.id = ids.reminder_id
  </code></pre>
  
  <p>I get an error <code>missing FROM-clause entry for table "reminder"</code>, because I'm including the <code>reminder.id</code> column in the <code>RETURNING</code> clause, but not actually selecting it for the insert. This makes sense, but I can't figure out how to modify the query to do what I need. Is there a totally different approach I'm missing?</p>