This discussion is archived
5 Replies Latest reply: Sep 10, 2013 9:57 AM by chillychin RSS

Merge vs Update with multiple tables and columns

chillychin Newbie
Currently Being Moderated

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 Expert
    Currently Being Moderated

    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 Newbie
    Currently Being Moderated

    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 Guru
    Currently Being Moderated

    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 Guru
    Currently Being Moderated

    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 Newbie
    Currently Being Moderated

    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

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points