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.
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
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
1 person found this helpful
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, 188.8.131.52.0).
See the forum FAQ: https://forums.oracle.com/message/9362002
1 person found this helpful
Take it as a template
merge into summ_snap ss
using (select ss.snap1,ac.adm1,sum(h.his1) sum_his1
from history h,
where h.his2 = ss.snap2
and ss.snap3 = ac.adm2
group by ss.snap1,ac.adm1
on (x.snap1 = ss.snap1 -- maybe
and x.adm1 = ss. -- you name it
set ss.snap3 = x.sum_his1
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