Why I get error SQL command not properly ended while I execute UPDATE query

Question

I am trying to migrate such a simple MySQL query to Oracle SQL using PLSQL

UPDATE submaterial SET `Name` = '{$name}'".($image !== null ? ", `Picture` = '{$image}'" : "")." WHERE SubmaterialID = {$id};

When I migrate to PLSQL I get something like this

UPDATE submaterial SET Name = p_name 
     CASE WHEN p_image != NULL THEN 
       Picture = p_image
     WHERE SubmaterialID = p_Id;

When I want to execute this I get error

Error(408,6): PL/SQL: ORA-00933: SQL command not properly ended

I try also something like this but also get same error message

 UPDATE submaterial SET Name = p_name 
     CASE WHEN  p_image IS NOT NULL THEN
       Picture = p_image
     WHERE SubmaterialID = p_Id;

Does anyone know where did I made mistake ? How to write this king of query to Oracle SQL ?

Littlefoot · Accepted Answer · 2020-11-26 11:58:15Z

1

That would be

UPDATE submaterial
   SET Name = p_name,
       picture = CASE WHEN p_image IS NOT NULL THEN p_image END
 WHERE SubmaterialID = p_Id;

i.e. you have to take picture = out of case expression.

answered Nov 26, 2020 at 11:58

Littlefoot

144k15 gold badges41 silver badges65 bronze badges

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

1 Answer 1