title: SQL updates to data layed out as linked list
body: <p><strong>* Updated, clarified question and example</strong></p>
  
  <p>I have a table that has data represented as a linked list</p>
  
  <p>ie:</p>
  
  <p>ID|FROM_STATE|TO_STATE|SOME_OTHER_COLUMN</p>
  
  <p>I need to change the "pointers" around to remove the state of BAR. As you see below, foo goes to bar and bar goes to com etc. I want foo to go directly to com and from QUAX to FIZZ. Additionally, the value from "SOME_OTHER_COLUMN" needs to be picked up from the record being removed and used in place of the value for "SOME_OTHER_COLUMN" on the record being updated.</p>
  
  <p>FROM</p>
  
  <pre><code>ID|FROM_STATE|TO_STATE|SOME_OTHER_COLUMN
   1| FOO      | BAR    | xxx
   1| BAR      | COM    | zzz
   2| QUAZ     | BAR    | aaa
   2| BAR      | FIZZ   | bbb
  </code></pre>
  
  <p>TO:</p>
  
  <pre><code>ID|FROM_STATE|TO_STATE|SOME_OTHER_COLUMN
   1| FOO      | COM    | zzz
   2| QUAX     | FIZZ   | bbb
  </code></pre>
  
  <p>I started by writing a self join and trying something like:</p>
  
  <pre><code>update 
  FOO a
      join FOO b 
        on a.ID = b.ID
       and a.FROM_STATE = b.FROM_STATE
  
    set a.TO_STATE = b.TO_STATE
  where b.FROM_STATE='BAR'
    and a.TO_STATE='BAR';
  </code></pre>
  
  <p>That didn't work, no rows were changed. </p>
  
  <p>I've setup my table in the below SQLFilldle. Any help is appreciated. DB is oracle (don't have the version)</p>
  
  <p><a href="http://sqlfiddle.com/#!2/7e8d08/1" rel="nofollow">http://sqlfiddle.com/#!2/7e8d08/1</a></p>