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:
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 that syntax to do the update, it doesn't 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 line 7 column 28 is the end of the "SET" line.
Oracle does not support JOIN
clause in UPDATE
statements.
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