This content has been marked as final. Show 5 replies
Samim wrote:The closing keyword that corresponds to "CASE" is "END", not "END *CASE* ".
I want to put case statement in an update statement using Oracle 10g
I'm getting a syntax error on the last line Ora-00933: SQL command not properly ended
UPDATE EMP a
SET EMP.TYPE = CASE
WHEN 'Y' = 'A' THEN 'DIV'
WHEN 'Y' = '1' THEN 'DEF'
The string 'Y' is never equal to 'A', or to '1'.
If you have a column named y in the table, then you might want to say:
I hope this answers your question.
UPDATE EMP SET EMP.TYPE = CASE WHEN Y = 'A' THEN 'DIV' WHEN Y = '1' THEN 'DEF' END;
If not, post a little sample data (CREATE TABLE and INSERT statemnts to re-create your emp table as it exists before the UPDATE), and the results you want from that data (that is, the contents of the emp table after the UPDATE).
This is slightly confusing but the syntax between sql and pl/sql is different for the case statement.
is needed in PLSQL whereas
is correct in SQL.
In addition to what Frank already said, you should only update those rows where the value needs to be updated.
Edited by: Sven W. on Aug 25, 2010 7:20 PM
UPDATE EMP SET EMP.TYPE = CASE WHEN Y = 'A' THEN 'DIV' WHEN Y = '1' THEN 'DEF' END WHERE (EMP.TYPE != CASE WHEN Y = 'A' THEN 'DIV' WHEN Y = '1' THEN 'DEF' END OR EMP.TYPE is null)
Edited by: Sven W. on Aug 25, 2010 7:22 PM -- added null condition