Oracle SQL Developer: If Exists Update Else Insert Missing Right Parenthesis Compiler Error

Question

I have been attempting to write a SQL Query in Oracle's SQL Developer that will update a row if it exists and insert one if it doesn't. Besides the primary key(UNITOFMEASURE_USERID), there are three columns: UNITOFMEASUREID, USERID, and ACTIVE. There is a unique constraint on combinations of UNITOFMEASUREID and USERID, so for each combination of values there is either one row or 0. I have been trying two different SQL arrangements, and both of them return a similar compilation error complaining about a missing right parenthesis. This is what I have tried: First arrangement:

UPDATE NORSEMEN.UNITOFMEASURE_USER SET (ACTIVE=1) WHERE UNITOFMEASUREID=:unitOfMeasureId AND USERID=:userId
IF @@ROWCOUNT=0
    INSERT INTO NORSEMEN.UNITOFMEASURE_USER (UNITOFMEASUREID, USERID) VALUES (:unitOfMeasureId, :userId)

SQL Developer error response for first arrangement:

Error at Command Line:1 Column:46
Error report:
SQL Error: ORA-00907: missing right parenthesis
00907. 00000 -  "missing right parenthesis"
*Cause:    
*Action:

Second arrangement:

IF EXISTS (SELECT * FROM NORSEMEN.UNITOFMEASURE_USER WHERE UNITOFMEASUREID=:unitOfMeasureId AND USERID=:userId)
    UPDATE NORSEMEN.UNITOFMEASURE_USER SET (ACTIVE = 1) UNITOFMEASUREID=:unitOfMeasureId AND USERID=:userId
ELSE
    INSERT INTO NORSEMEN.UNITOFMEASURE_USER (UNITOFMEASUREID, USERID) VALUES (:unitOfMeasureId, :userId)

SQL Developer error response for second arrangement:

Error at Command Line:2 Column:47
Error report:
SQL Error: ORA-00907: missing right parenthesis
00907. 00000 -  "missing right parenthesis"
*Cause:    
*Action:

I have not been able to figure out why SQL Developer thinks there is a missing parenthesis. Can anyone help?

Answer 1

In your case there is a little need of using any procedural processing to update active column of the table if records matching condition is true, or insert new record if it's not. You can do it in a single statement using merge.

merge 
 into unitofmeasure_user
using dual
  on (
      unitofmeasureid = :unitOfMeasureId AND 
      userid = :userId
     )
when matched 
then update 
        set active = 1
when not matched
then insert (unitofmeasureid, userid) 
       values (:unitOfMeasureId, :userId);