Self-Learning Update

Question

I want to update the table to indicate that some rows are parents of others, so I added the “parentid” column to the table. The following query finds all parents:

SELECT ca1.id, ca2.id FROM contactassociations ca1 JOIN contactassociations ca2 ON (ca1.contactid = ca2.contactid) where ca1.entitytable = 'EMPLOYER' AND ca2.entitytable = 'CLIENT'; 

but when I try to adapt this syntax for updating, it does not work:

 UPDATE contactassociations ca1 SET ca1.parentid = ca2.id JOIN contactassociations ca2 ON (ca1.contactid = ca2.contactid) WHERE ca1.entitytable = 'EMPLOYER' AND ca2.entitytable = 'CLIENT'; 

I get:

 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: 

Note that row 7 of column 28 is the end of row “SET”.

Answer 1

Oracle does not support the JOIN clause in UPDATE .

Use this:

 MERGE INTO contactassociations ca1 USING contactassociations ca2 ON ( ca1.contactid = ca2.contactid AND ca1.entitytable = 'EMPLOYER' AND ca2.entitytable = 'CLIENT' ) WHEN MATCHED THEN UPDATE SET parentid = ca2.id 

Answer 2

I find the following style easier to read, but you need to use an alias after the UPDATE keyword, not a table name:

 UPDATE ca1 SET ca1.parentid = ca2.id FROM contactassociations ca1 LEFT JOIN contactassociations ca2 ON (ca1.contactid = ca2.contactid) WHERE ca1.entitytable = 'EMPLOYER' AND ca2.entitytable = 'CLIENT' 

Answer 3

 -- Method #1 update emp set MANAGERNAME= mgr.EMPNAME FROM SelfJoinTable emp , SelfJoinTable mgr where emp.MANAGERID = mgr.EMPID -- Method #2 update emp set MANAGERNAME= mgr.EMPNAME FROM SelfJoinTable emp LEFT OUTER JOIN SelfJoinTable mgr ON emp.MANAGERID = mgr.EMPID -- Method #3 update emp set MANAGERNAME= mgr.EMPNAME FROM SelfJoinTable emp JOIN SelfJoinTable mgr ON emp.MANAGERID = mgr.EMPID -- Method #4 update emp set MANAGERNAME= mgr.EMPNAME FROM SelfJoinTable emp inner JOIN SelfJoinTable mgr ON emp.MANAGERID = mgr.EMPID