Boerejef Boerejef - 3 months ago 14
SQL Question

ORA-00905 when running update statement

I have following update statement in Oracle

UPDATE PAY_PMT
SET INTER_COMPANY = CASE INTER_COMPANY_ID
WHEN 1 THEN 'Y';
ELSE 'N';
END CASE;
WHERE ID != 1
/


But when I execute this, I get following error message:ORA-00905: missing keyword

What is wrong?

Answer

You just used 3 semicolons in an update. In this case it means you have 4 statements. The first is

UPDATE PAY_PMT
SET INTER_COMPANY = CASE INTER_COMPANY_ID
WHEN 1 THEN 'Y';

The second is:

ELSE 'N';

The third is:

END CASE;

And the fourth is:

WHERE ID != 1

the solution is to use semicolon only at the end of the statement, and the case was not correct.

 UPDATE PAY_PMT
    SET INTER_COMPANY = CASE
        WHEN INTER_COMPANY_ID = 1 THEN 'Y'
        ELSE 'N'
    END
 WHERE ID != 1;
Comments