title: INSERT rows into multiple tables in a single query, selecting from an involved table
body: <p>I have two tables of the following form (i.e., every foo is linked to exactly one bar).</p>
  
  <pre><code>CREATE TABLE foo (
      id INTEGER PRIMARY KEY,
      x INTEGER NOT NULL,
      y INTEGER NOT NULL,
      ...,
      bar_id INTEGER UNIQUE NOT NULL,
      FOREIGN key (bar_id) REFERENCES bar(id)
  );
  
  CREATE TABLE bar (
      id INTEGER PRIMARY KEY,
      z INTEGER NOT NULL,
      ...
  );
  </code></pre>
  
  <p>It's easy to copy rows in <code>foo</code> which meet a particular condition using a nested query:</p>
  
  <pre><code>INSERT INTO foo (...) (SELECT ... FROM foo WHERE ...)
  </code></pre>
  
  <p>But I can't figure out how to make a copy of the associated row in <code>bar</code> for each row in <code>foo</code> and insert the id of <code>bar</code> into the new <code>foo</code> row. Is there any way of doing this in a single query?</p>
  
  <p>Concrete example of desired result:</p>
  
  <pre><code>-- Before query:
  
  foo(id=1,x=3,y=4,bar_id=100)  .....  bar(id=100,z=7)
  foo(id=2,x=9,y=6,bar_id=101)  .....  bar(id=101,z=16)
  foo(id=3,x=18,y=0,bar_id=102) .....  bar(id=102,z=21)
  
  
  -- Query copies all pairs of foo/bar rows for which x&gt;3:
  
  -- Originals
  foo(id=1,x=3,y=4,bar_id=101)  .....  bar(id=101,z=7)
  foo(id=2,x=9,y=6,bar_id=102)  .....  bar(id=102,z=16)
  foo(id=3,x=18,y=0,bar_id=103) .....  bar(id=103,z=21)
  
  -- "Copies" of foo(id=2,...) and foo(id=3,...), with matching copies of
  -- bar(id=102,...) and bar(id=103,...)
  foo(id=4,x=9,y=6,bar_id=104)  .....  bar(id=104,z=16)
  foo(id=5,x=18,y=0,bar_id=105) .....  bar(id=105,z=21)
  </code></pre>