title: Update with self-join
body: <p>I want to update a table to indicate that some rows are parents of others, so I added a "parentid" column to the table.  The following query finds all the parents:</p>
  
  <pre><code>SELECT ca1.id, ca2.id 
  FROM contactassociations ca1
  JOIN contactassociations ca2 ON (ca1.contactid = ca2.contactid)
  where ca1.entitytable = 'EMPLOYER' AND
  ca2.entitytable = 'CLIENT';
  </code></pre>
  
  <p>but when I try to adapt that syntax to do the update, it doesn't work:</p>
  
  <pre><code>UPDATE contactassociations ca1
  SET    ca1.parentid = ca2.id
  JOIN  contactassociations ca2 ON (ca1.contactid = ca2.contactid)
  WHERE ca1.entitytable = 'EMPLOYER' AND ca2.entitytable = 'CLIENT';
  </code></pre>
  
  <p>I get:</p>
  
  <pre><code>Error starting at line 6 in command:
  UPDATE contactassociations ca1
  SET    ca1.parentid = ca2.id
  JOIN  contactassociations ca2 ON (ca1.contactid = ca2.contactid)
  WHERE ca1.entitytable = 'EMPLOYER' AND ca2.entitytable = 'CLIENT'
  Error at Command Line:7 Column:28
  Error report:
  SQL Error: ORA-00933: SQL command not properly ended
  00933. 00000 -  "SQL command not properly ended"
  *Cause:    
  *Action:
  </code></pre>
  
  <p>Note that line 7 column 28 is the end of the "SET" line.</p>