Oracle SQL - Update query with NOT EXISTS does not work

1
votes

I think I am going wrong with Oracle SQL syntax. This is the syntax I use in Transact-SQL. 

UPDATE "DE_OPS"
SET IMPORT_DATE = GETDATE()
WHERE PROCEDURE_CODE NOT IN ( SELECT DISTINCT PROCEDURE_CODE FROM DE_OPS_20140730 );

I reframed this for Oracle SQL as 

UPDATE "DE_OPS"
 SET IMPORT_DATE = SYSDATE()
WHERE PROCEDURE_CODE NOT EXISTS ( SELECT DISTINCT PROCEDURE_CODE FROM DE_OPS_20140730);

But this gives the error

Error at Command Line : 3 Column : 26 Error report - SQL Error: ORA-00933: SQL command not properly ended 00933. 00000 - "SQL command not properly ended" *Cause:
*Action:

Not sure where I am going wrong. Does "not exists" allow subqueries?

2
sqloracleoracle-sqldeveloper
I hope you are ware that the way you use NOT EXISTS changes what the statement does. The first one updates all rows with a matching procedure_code. The second one updates all rows if there is at no row DE_OPS_20140730 at all. Why did you change the NOT IN to NOT EXISTS in the first place? The syntax error however is unrelated to that: it's SYSDATE not sysdate(). - a_horse_with_no_name
Using the NOT IN is fine with Oracle, just use SYSDATE instead of GETDATE() - Gerrat
In any case, you don't need the DISTINCT keyword in your subquery. The outer query will only return one row per value in the NOT IN and NOT EXISTS subqueries, and including DISTINCT only adds overhead. - Wolf

2 Answers

1
votes

There is no need of parentheses after sysdate. Try:

UPDATE "DE_OPS"
 SET IMPORT_DATE = SYSDATE
WHERE PROCEDURE_CODE NOT EXISTS ( SELECT DISTINCT PROCEDURE_CODE FROM DE_OPS_20140730);
0
votes

The correct syntax is either NOT EXISTS or NOT IN. You're mixing the two.

UPDATE "DE_OPS"
 SET IMPORT_DATE = SYSDATEECT DISTINCT PROCEDURE_CODE FROM DE_OPS_20140730);

OR

UPDATE "DE_OPS" d
 SET IMPORT_DATE = SYSDATE
WHERE NOT EXISTS (SELECT DISTINCT PROCEDURE_CODE FROM DE_OPS_20140730 WHERE PROCEDURE_CODE = d.PROCEDURE_CODE);