5 Replies Latest reply: Aug 26, 2010 1:36 AM by Efficientoracle RSS

    Update statement with Case syntax

    469636
      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

      Help please

      UPDATE EMP a
      SET EMP.TYPE = CASE
      WHEN 'Y' = 'A' THEN 'DIV'
      WHEN 'Y' = '1' THEN 'DEF'
      END CASE;
        • 1. Re: Update statement with Case syntax
          bluefrog
          no need for END CASE; simply END; will do

          http://download.oracle.com/docs/cd/E11882_01/server.112/e10592/expressions004.htm#SQLRF20037

          Edited by: bluefrog on Aug 25, 2010 5:02 PM
          • 2. Re: Update statement with Case syntax
            SomeoneElse
            What are you trying to accomplish with this?
            WHEN 'Y' = 'A' THEN 'DIV'
            WHEN 'Y' = '1' THEN 'DEF'
            Those will never be true.
            • 3. Re: Update statement with Case syntax
              Frank Kulash
              Hi,
              Samim wrote:
              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

              Help please

              UPDATE EMP a
              SET EMP.TYPE = CASE
              WHEN 'Y' = 'A' THEN 'DIV'
              WHEN 'Y' = '1' THEN 'DEF'
              END CASE;
              The closing keyword that corresponds to "CASE" is "END", not "END *CASE* ".

              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:
              UPDATE EMP
              SET EMP.TYPE = CASE 
                                  WHEN Y = 'A' THEN 'DIV'
                                  WHEN Y = '1' THEN 'DEF'
                            END;
              I hope this answers your question.
              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).
              • 4. Re: Update statement with Case syntax
                Sven W.
                This is slightly confusing but the syntax between sql and pl/sql is different for the case statement.
                END CASE
                is needed in PLSQL whereas
                END
                is correct in SQL.

                In addition to what Frank already said, you should only update those rows where the value needs to be updated.
                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:20 PM

                Edited by: Sven W. on Aug 25, 2010 7:22 PM -- added null condition
                • 5. Re: Update statement with Case syntax
                  Efficientoracle
                  This Will Work.


                  UPDATE emp a
                  SET emp.TYPE = CASE
                  WHEN Y = 'A'
                  THEN 'DIV'
                  WHEN Y = '1'
                  THEN 'DEF'
                  END

                  Edited by: 790852 on Aug 25, 2010 11:36 PM