5 Replies Latest reply: Sep 10, 2013 11:57 AM by chillychin RSS

    Merge vs Update with multiple tables and columns

    chillychin

      I am trying to update multiple columns from one table based on the results of another table

       

      So I have 3 tables as follows

       

      HISTORY

      SUMM_SNAP

      ADM_CHOICE

       

      My SQL code is loosely

       

      SELECT SUM(H.HIS1), SS.SNAP1, AC.ADM1

      FROM

      HISTORY H,

      SUMM_SNAP SS,

      ADM_CHOICE AC

      WHERE H.HIS2=SS.SNAP2

      AND SS.SNAP3=AC.ADM2

      GROUP BY SS.SNAP1, AC.ADM1

       

      This works, and I am able to SUM the column as I need with the right numbers.

       

      I altered the SUMM_SNAP table and now I want this summarized column to be in the table

       

      I tried using UPDATE, but there is no FROM clause to let me do the table join/group by

       

      UPDATE SUMM_SNAP

      SET SUMM_SNAP.SNAP3=SUM(H.HIS1)

      FROM

      HISTORY H,

      SUMM_SNAP SS,

      ADM_CHOICE AC

      WHERE H.HIS2=SS.SNAP2

      AND SS.SNAP3=AC.ADM2

      GROUP BY SS.SNAP1, AC.ADM1

       

      The above is obviously wrong - but just trying to show whatI was thinking

       

      What would be the best method to get the numbers from the SUM into a table?

        • 1. Re: Merge vs Update with multiple tables and columns
          Greg Spall

          Hmm, when I do these more complex updates, I do personally prefer MERGE (no real reason other than I find it is generally simpler to code/read).

           

          You're situation, however, is a bit tricky, due to that SUM and group. I need more info, please.

           

          Can you re-write the SELECT query and include the ROW_ID from SUMM_SNAP ??

           

          Is the SNAP1 the PK on that table?  If so, then ROW_ID should be able to be dropped beside it.

          That ADM1 column, however, I'm not sure of - might screw things up.  It might cause a duplicate row on the SNAP1 PK (and hence duplicate ROWID).

           

          You need to end up with a single occurance of each "lookup value" you want to use (either PK, or ROW_ID, or whatever ..).

           

          Then we can use that to join into the merge.  That's the hard part, putting the merge together after you figure that out is easy.

           

          And hopefully you know your data enough (and can understand my ramblings enough ) to put that together.

          • 2. Re: Merge vs Update with multiple tables and columns
            chillychin

            Im not too sure what do you mean by include the ROW_ID?

             

            Do you mean something along the lines of

             

            SELECT SUM(H.HIS1), SS.ROW_NUM, SS.SNAP1, AC.ADM1

            FROM

            HISTORY H,

            SUMM_SNAP SS,

            ADM_CHOICE AC

            WHERE H.HIS2=SS.SNAP2

            AND SS.SNAP3=AC.ADM2

            GROUP BY SS.SNAP1, AC.ADM1

             

            The query I provided is actually just a really reduced version of the one I have but just showing the main issues (as you mentioned the SUM/GROUP BY complicated things a lot)

             

            With the joins from all 3 tables, I do get a distinct/unique row so AC_ADM1 isnt really needed. The main thing was just the SUM in the SELECT

            • 3. Re: Merge vs Update with multiple tables and columns
              Frank Kulash

              Hi,

               

              The syntax for using a sub-query in an UPDATE statement goes like this:

               

              UPDATE  summ_snap    m

              SET     snap3 =

                      (

                          SELECT  SUM (H.HIS1)

                          ...

                      )

              ;

              That is, the entire sub-query, including the SELECT keyword, goes inside parentheses.  You almost always want to correlate the sub-query to the main UPDATE statement by using the table alias (m. in this case) somewhere in the sub-query.

               

              As Greg said, MERGE is often much simpler, and more efficient, than UPDATE, especially if you use aggregate functions like SUM.

               

              Whenever you have a problem, please post a little sample data (CREATE TABLE and INSERT statements, relevant columns only) from all tables involved, so that the people who want to help you can re-create the problem and test their ideas.

              Also post the results you want from that data, and an explanation of how you get those results from that data, with specific examples.

              If you're asking about a DML statement, such as UPDATE, the CREATE TABLE and INSERT statements should re-create the tables as they are before the DML, and the results  will be the contents of the changed table(s) when everything is finished.

              Always say which version of Oracle you're using (for example, 11.2.0.2.0).

              See the forum FAQ: https://forums.oracle.com/message/9362002

              • 4. Re: Merge vs Update with multiple tables and columns
                Etbin

                Take it as a template

                 

                merge into summ_snap ss

                using (select ss.snap1,ac.adm1,sum(h.his1) sum_his1

                         from history h,

                              summ_snap ss,

                              adm_choice ac

                        where h.his2 = ss.snap2

                          and ss.snap3 = ac.adm2

                        group by ss.snap1,ac.adm1

                      ) x

                   on (x.snap1 = ss.snap1  -- maybe

                  and  x.adm1 = ss.        -- you name it

                      )

                when matched

                then update

                         set ss.snap3 = x.sum_his1

                 

                Regards

                 

                Etbin

                • 5. Re: Merge vs Update with multiple tables and columns
                  chillychin

                  Thank you for the suggestions

                   

                  I think both update and merge would have worked in this case, but the amount of my data was what was preventing me from properly testing