2 Replies Latest reply on Jun 19, 2014 9:42 AM by Frank Kulash

    Add value to existing data in table

    847750

      Hi All,

       

      We have a requirement where if sum of data is not equal to 0, we should add the delta to existing data of first record.

       

      Eg:

       

      Current

       

      Account       Entity    Data

      AC_25001    EN01    10.25

      AC_25002    EN02    -10.20

       

      Variance - 0.05 .This should be subtracted from Debit value

       

      Result should be

       

      Account       Entity    Data

      AC_25001    EN01    10.20  (10.25 - 0.05)

      AC_25002    EN02    -10.20

       

      This results in 0 total.

       

      Can anyone provide inputs on this

       

      Thanks in advance

       


        • 1. Re: Add value to existing data in table
          Moazzam

          Though not clear about your requirement, try this:

           

          UPDATE T

          set DATA = DATA - (SELECT sum(DATA) FROM T)

          WHERE DATA > 0

           

          Message was edited by: Moazzam

          • 2. Re: Add value to existing data in table
            Frank Kulash

            Hi,

             

            I would use MERGE for that.

            Since I don't have a copy of your table, I used my own dept table (which started as a copy of the scott.dept table) to illustrate:

             

            MERGE INTO  dept    dst

            USING (

                      SELECT  MIN (dname)     AS first_dname

                      ,       SUM (deptno)    AS total_deptno

                      FROM    dept

                  )             src

            ON    ( dst.dname  = src.first_dname )

            WHEN MATCHED THEN UPDATE

            SET     dst.deptno  = NVL (dst.deptno, 0) - src.total_deptno

            ;

             

            Contents of the table after everything is finshed:

             

                DEPTNO DNAME          LOC

            ---------- -------------- -------------

                   -90 ACCOUNTING     NEW YORK

                    40 OPERATIONS     BOSTON

                    20 RESEARCH       DALLAS

                    30 SALES          CHICAGO

             

            "First" in this example means "first in alphabetic order by dname".