1 Reply Latest reply: Jan 28, 2013 12:00 PM by P.Forstmann RSS

    ORA-00907 update if case

    915922
      can we do multiple columns in the update below

      update MN_NW
           set fcode =
           CASE      when (Tollrd is not null) then fcode + 500
                when (stubble <> 0)     then     28000
                when (backrd in ( 1,2)) then 29016
           Else     
                (20000 + (FRC * 1000) + FOW)
           END ,
           fcode_char = to_char(fcode),
           micode =
           CASE (when feattyp=4165) then 20090400
                (when feattyp=4130) then 70020100
           Else
                null
           End ;

      this is 11.2.0.3.0 oracle std edition one for one of our databases

      Edited by: 912919 on Jan 28, 2013 8:38 AM
        • 1. Re: ORA-00907 update if case
          P.Forstmann
          Yes you can but you need to check where to put parenthesis and to not forget to use a operator when using mn_nw.stubble column:
          SQL> select * from v$version;
          
          BANNER
          --------------------------------------------------------------------------------
          Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
          PL/SQL Release 11.2.0.1.0 - Production
          CORE    11.2.0.1.0      Production
          TNS for Linux: Version 11.2.0.1.0 - Production
          NLSRTL Version 11.2.0.1.0 - Production
          
          SQL> drop table mn_nw purge;
          
          Table dropped.
          
          SQL> create table mn_nw (
            2   fcode number,
            3   tollrd number,
            4   stubble number,
            5   backrd number,
            6   frc number,
            7   fow number,
            8   feattyp number,
            9   micode number,
           10   fcode_char varchar2(20)
           11  );
          
          Table created.
          
          SQL>
          SQL> update mn_nw
            2  set fcode =
            3  case
            4   when (tollrd is not null) then fcode + 500
            5   when (stubble     > 0) then 28000
            6   when (backrd in ( 1,2)) then 29016
            7  else
            8   (20000 + (frc * 1000) + fow)
            9  end,
           10  fcode_char = to_char(fcode),
           11  micode =
           12  case
           13   when (feattyp=4165) then 20090400
           14   when (feattyp=4130) then 70020100
           15  else
           16   null
           17  end;
          
          0 rows updated.
          
          SQL>