title: Cloning a record and dividing up its children between the two of them
body: <p>I have two tables that look something like the following:</p>
  
  <pre><code> Foo
   ---
   FooID  FooType
   -----  -------
   1      Red
   2      Red
   3      Green
   4      Red
   5      Blue
   6      Red
  
   Bar
   ---    
   BarID  BarFooID  BarData
   -----  --------  -------
   1      1         A
   2      1         B
   3      2         C
   4      2         D
   5      3         E
   6      3         F
   7      3         G
   8      3         H
  </code></pre>
  
  <p>BarID's 6 and 7 were mistakenly associated with FooID3 and should have been associated with a different Foo, with a different Foo Type. I have BarID-6 &amp; 7 listed in a separate table (BadBar)</p>
  
  <p>What I'd like to do is copy FooID-3 to a new record (FooID-7) and then repoint BarID-6 &amp; 7's BarFooID at FooID-7, and then update FooID-7's FooType to its new value.</p>
  
  <p>My expected out put would look something like this:</p>
  
  <pre><code> Foo
   ---
   FooID  FooType
   -----  -------
   1      Red
   2      Red
   3      Green
   4      Red
   5      Blue
   6      Red
   7      Purple     // new row
  
   Bar
   ---    
   BarID  BarFooID  BarData
   -----  --------  -------
   1      1         A
   2      1         B
   3      2         C
   4      2         D
   5      3         E
   6      7         F        // updated
   7      7         G        // updated
   8      3         H
  </code></pre>
  
  <p>I can imagine how to do this in pseudo code:</p>
  
  <pre><code>For Each Bar in BadBars
      copy Bar's Foo to a new Foo
      remember the new Foo's FooID
      update the new Foo's FooType
      update Bar's BarFooID to the remembered FooID
  End For
  </code></pre>
  
  <p><strong>Is there a way I can create an SQL transaction to do this</strong> as one operation or at least clone the Foo and relink the Bar to the clone (I can always make a second pass to update the clones).</p>
  
  <p>Or am I stuck writing a one off script for this?</p>