Oracle SQL - Update query with NOT EXISTS does not work

Question

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?

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(). — user330315
– user330315, Commented Jul 30, 2014 at 15:01
Using the NOT IN is fine with Oracle, just use SYSDATE instead of GETDATE() — Gerrat
– Gerrat, Commented Jul 30, 2014 at 15:02
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. — WoMo
– WoMo, Commented Jul 30, 2014 at 15:20

Joseph B · Accepted Answer · 2014-07-30 15:00:42Z

1

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

answered Jul 30, 2014 at 15:00

Joseph B

5,7291 gold badge17 silver badges19 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Scott · Accepted Answer · 2014-07-30 15:02:51Z

0

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

answered Jul 30, 2014 at 15:02

Scott

3,7629 gold badges36 silver badges60 bronze badges

Collectives™ on Stack Overflow

Oracle SQL - Update query with NOT EXISTS does not work

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related