Oracle SQL Developer: if an update exists only if there is no compiler error on the right side of the brackets

Question

I am trying to write SQL Query in Oracle SQL Developer that will update a row if it exists and insert it if it does not. In addition to the primary key (UNITOFMEASURE_USERID), there are three columns: UNITOFMEASUREID, USERID and ACTIVE. There is a unique restriction on the combination of UNITOFMEASUREID and USERID, so for each combination of values ​​there is either one row or 0. I try to execute two different SQL procedures, and both of them return a similar compilation error, complaining about the missing right parentheses. Here is what I tried: First location:

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 Developer response error for the first build:

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

Second location:

 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 Developer response error for the second build:

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

I could not understand why SQL Developer considers that the bracket is missing. Can anyone help?

Answer 1

In your case, there is little need to use procedural processing to update the active column of the table if the record matching condition is true or insert a new record if it is not. You can do this in one 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); 

Answer 2

You have a syntax error that will give you the exact error message;

 UPDATE NORSEMEN.UNITOFMEASURE_USER SET (ACTIVE=1) WHERE ... 

it should be

 UPDATE NORSEMEN.UNITOFMEASURE_USER SET ACTIVE=1 WHERE ... 

In the second layout, you also lack WHERE .