3 Replies Latest reply on May 20, 2017 12:20 AM by Mike Kutz

    Conditional update in PL/SQL

    Jian-cdo

      Hello,

       

      When I pass value to application process, I'd like to do this: for apex_application.g_x01, if it's not null, then update column A; if not, don't update the column.  So I wonder if there is some simpler query way to handle it (not NVL() since there is no default value to be assigned).  For right now, I just use if apex_application.g_x01 is null, one update query; if not null, another update query.  It's kind of repeating itself, not neat...  thanks!

        • 1. Re: Conditional update in PL/SQL
          Mike Kutz

          PL/SQL can't do updates.

          Only SQL can.

           

          What are you using for your current UPDATE statement?

           

          MK

          • 2. Re: Conditional update in PL/SQL
            Jian-cdo

            Hi Mike,

             

            Sorry I didn't make it clear.  It's pl/sql code (which allows me to use if else statement), but I want to have only one update statement instead having two statements for if/else like the following code: (since it's not just one parameter like apex_application.g_x01, I also need to evaluate other parameters to decide if update other columns or not, that will lead to multiple levels of if/else statements).  thanks.

             

            if apex_application.g_x01 is not null then

            update table A

            set columnA='abc', columnB='123'

            else

            update table A

            set columnB='123'

            • 3. Re: Conditional update in PL/SQL
              Mike Kutz

              Use a CASE ,DECODE, or NVL2.

               

              UPDATE TABLE_TO T

              Set columnA=decode (apex_application.g_x01, null, columnA, 'abc' )

                ColumnB=123;

               

              UPDATE TABLE_TO T

              Set columnA = case

                when apex_application.g_x01 is not null then 'abc'

                Else columnA

                End,

                ColumnB=123;

               

              UPDATE TABLE_T T

              SET ColumnA=nvl2 (apex_application.g_x01, 'abc', columnA),

                 ColnB=123;

               

              Double check NVL2 () context, I might have them backwards.

               

              MK

               

              Edit: corrected CASE syntax