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?

As well as Joachim's observation, `@@rowcount` is not Oracle syntax. Presumably you're doing this in a PL/SQL block, given the second error is against line 2. Either way, you might want to consider the [`merge` statement](http://docs.oracle.com/cd/E11882_01/server.112/e10592/statements_9016.htm) to do an 'upsert'. See [this](http://stackoverflow.com/q/237327/266304) for example. — Alex Poole, Oct 05 '12 at 22:24
All your procedural code looks like Microsofts SQL Server's T-SQL, not Oracle's PL/SQL. For example in PL/SQL an `if` statement with no `else` is `IF condition THEN <> END IF;` With an else `IF condition THEN <> ELSE <> END IF;` I recommend you look for a different reference materials, tutorials, and/or examples to learn PL/SQL from. Free reference: http://docs.oracle.com/cd/E11882_01/appdev.112/e25519/toc.htm. Not free tutorial: http://www.amazon.com/dp/0596514468 — Shannon Severance, Oct 06 '12 at 23:10
And Oracle's PL/SQL does not allow subqueries in conditionals of `if` statements. So second would be `SELECT count(*) INTO :rowCount FROM NORSEMEN.UNITOFMEASURE_USER WHERE UNITOFMEASUREID=:unitOfMeasureId AND USERID=:userId and ROWNUM <= 1; IF :rowCount > 0 THEN ... ELSE ... END IF;` Just use `merge`. — Shannon Severance, Oct 06 '12 at 23:17
Thank you all for your comments. They have been instructive, and I understand better now why my SQL query was not compiling. Thanks! — James Dunn, Oct 08 '12 at 16:30

Nick Krasnov · Accepted Answer · 2012-10-06T20:04:12.387

6

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);

edited Oct 06 '12 at 20:04

answered Oct 05 '12 at 22:27

Nick Krasnov

26,886
6
61
78

Hey, thanks Nocholas! I've tried your solution, and it does exactly what I wanted it to do. Thanks for the help! – James Dunn Oct 08 '12 at 16:31

score 2 · Answer 2 · answered Oct 05 '12 at 22:07

2

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

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

should be

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

In the second arrangement, you're also missing a WHERE.

answered Oct 05 '12 at 22:07

Joachim Isaksson

176,943
25
281
294

Thanks Joachim. I didn't realize that I shouldn't use parentheses there. – James Dunn Oct 08 '12 at 16:32

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

2 Answers2