Oracle SQL : update query a column with a subquery select with sum

Question

I try to execute this request

UPDATE table1 t1
   SET c1 = (SELECT c2
                FROM (SELECT key, SUM(c4 - c3) AS c2
                        FROM view2
                       GROUP BY key
                       ORDER BY key) t2
               WHERE t1.key = t2.key)

but it's very very long ...

I try with "with" but it's the same thing, merge but it's not the solution

I do not wish use PL/SQL

Thanks for your help

Do you have a another solution?

If I try

MERGE INTO table1 t1
    USING (SELECT id ,sum(c4- c3) AS result FROM view3 group by id order by id) t2
    ON (t1.id= t2.id)
  WHEN MATCHED THEN
    UPDATE SET t1.result = t2.result ;

I have this message : ORA-03113 fin de fichier sur canal de communication

Answer 1

Why do you select the c2 for all keys, only to dismiss them all and only use the key matching t1? Your query can be rewritten to:

UPDATE table1 t1
 SET c1 = (SELECT SUM(v2.c4 - v2.c3)
           FROM view2 v2
           WHERE v2.key = t1.key)

Then only make sure that the view is fast on selecting via the key, i.e. provide an index or multiple indexes to allow a quick lookup.

But why do you want to store this value in the table, if you can always select it from the view? If you want this to be and remain a mere copy, this is a bad idea. If you want this as initial values that can later change independent from the view's result, it is okay.