CODEWITHSUNDEEP
sqloracle-database
cyber101
cyber101

Reputation: 2994

Oracle Update Query missing SET keyword - ORA97100 & ORA-00904

Im getting a confusing "ORA97100 missing SET keyword" error when trying to run this simple UPDATE statement, although the "set" keyword is there:

UPDATE CURRENT_LOAD as ac
SET ac.LOAD_START_TIMESTAMP = CURRENT_TIMESTAMP
WHERE ac.LOAD_START_TIMESTAMP IS NULL;

I also tried, which gives "invalid identifier ORA-00904 AC.LOAD_START_TIMESTAMP"

UPDATE CURRENT_LOAD ac
SET ac.load_start_timestamp = CURRENT_TIMESTAMP
WHERE ac.load_start_timestamp IS NULL;

I further tried, which gives again "invalid identifier ORA-00904 CURRENT_LOAD.LOAD_START_TIMESTAMP"

UPDATE CURRENT_LOAD 
SET CURRENT_LOAD.load_start_timestamp = CURRENT_TIMESTAMP
WHERE CURRENT_LOAD.load_start_timestamp IS NULL;

This is really odd since the attribute "LOAD_START_TIMESTAMP" is actually defined in my table , see the "CURRENT_LOAD" table DDL below:

 CREATE TABLE "CURRENT_LOAD"
   (
  "LOAD_START_TIMESTAMP" TIMESTAMP with TIME ZONE, 
    "CONTRACT_NO" VARCHAR2(100 BYTE), 
    "PROJECT_DEFINITION" VARCHAR2(100 BYTE), 
    "MASTER_CONTRACT_NO" VARCHAR2(100 BYTE), 
    "Sac_CONTRACT_NO" VARCHAR2(100 BYTE)
);

Any well thought advise will be appreciated.

Thanks

Upvotes: 0

Views: 16462

Answers (1)

Rahul
Rahul

Reputation: 77934

Try running the UPDATE statement without table alias ac like

UPDATE CURRENT_LOAD
SET LOAD_START_TIMESTAMP = CURRENT_TIMESTAMP
WHERE LOAD_START_TIMESTAMP IS NULL;

Also, the problem is with AS before table alias. Your UPDATE statement should look like

UPDATE CURRENT_LOAD ac
SET ac.LOAD_START_TIMESTAMP = CURRENT_TIMESTAMP
WHERE ac.LOAD_START_TIMESTAMP IS NULL;

See Oracle documentation for more information.

Upvotes: 2

Related Questions