Modern SQL: With – Organize Complex Queries
A common pattern I still haven&#x27;t solved is this: I have table <i>A</i>, and now I want to add table <i>B</i> and give it one row for each row in table <i>A</i>. Then I want to add a foreign key column to table <i>A</i> pointing at the row I just added.<p>Of course this is easy to do with a loop, but can you do it all in pure SQL? If you have a solution I would love to see it.<p>Here is my history of attempts:<p><pre><code>    DROP TABLE IF EXISTS dogs;
    DROP TABLE IF EXISTS doghouses;
    DROP SEQUENCE IF EXISTS dogs_id_seq;
    DROP SEQUENCE IF EXISTS doghouses_id_seq;


    BEGIN;

    CREATE SEQUENCE dogs_id_seq;
    CREATE SEQUENCE doghouses_id_seq;

    CREATE TABLE dogs (
      id INTEGER PRIMARY KEY DEFAULT nextval(&#x27;dogs_id_seq&#x27;),
      name TEXT NOT NULL
    );

    INSERT INTO dogs
    (name)
    VALUES
    (&#x27;Sparky&#x27;),
    (&#x27;Spot&#x27;)
    ;

    -- Now we want to give each dog a doghouse:
    CREATE TABLE doghouses (
      id INTEGER PRIMARY KEY DEFAULT nextval(&#x27;doghouses_id_seq&#x27;),
      name TEXT NOT NULL
    );
    ALTER TABLE dogs ADD COLUMN doghouse_id INTEGER REFERENCES doghouses (id);

    &#x2F;*
    -- ERROR:  syntax error at or near &quot;INTO&quot;
    UPDATE  dogs AS d
    SET     doghouse_id = (
      INSERT INTO doghouses
      (name) VALUES (d.name)
      RETURNING id
    )
    ;
    *&#x2F;

    &#x2F;*
    -- ERROR:  WITH clause containing a data-modifying statement must be at the top level
    UPDATE  dogs AS d
    SET     doghouse_id = (
      WITH x AS (
        INSERT INTO doghouses
        (name) VALUES (d.name)
        RETURNING id
      ) SELECT * FROM x
    )
    ;
    *&#x2F;

    &#x2F;*
    -- ERROR:  missing FROM-clause entry for table &quot;dogs&quot;
    WITH homes AS (
      INSERT INTO doghouses
      (name)
      SELECT  name
      FROM    dogs
      RETURNING doghouses.id AS doghouse_id, dogs.id AS dog_id
    )
    UPDATE  dogs
    SET     doghouse_id = homes.doghouse_id
    FROM    homes
    WHERE   dogs.id = homes.dog_id
    ;
    *&#x2F;

    -- ERROR:  syntax error at or near &quot;INTO&quot;
    UPDATE  dogs AS d1
    SET     doghouse_id = h.id
    FROM    dogs d2
    INNER JOIN LATERAL (
      INSERT INTO doghouses
      (name) VALUES (d2.name)
      RETURNING id
    )
    ON true
    WHERE d1.id = d2.id
    ;


    COMMIT;</code></pre>