Reputation: 29
I have 3 tables A(a1, a2) , B (b1, b2), C (c1, c2). I want to perform update in the following way:
UPDATE A
SET A.a2 = (SELECT 2* SUM(C.c2)
FROM B JOIN C
ON B.b1 = C.c1 WHERE A.a1 = B.b2)
WHERE A.a1 = (SELECT B.b2
FROM B JOIN C
ON B.b1 = C.c1 HAVING SUM(C.c2) > 1000);
The problem with this approach is the tables B and C need to be joined multiple times.
Is there any good solution to solve this problem?
I tried:
UPDATE A SET A.a2 = (SELECT CASE
WHEN SUM(C.c2) > 1000 THEN 2*SUM(C.c2)
ELSE A.a2
END
FROM B JOIN C
ON B.b1 = C.c1
WHERE A.a1 = B.b2);
but it does not use the index on a1 and also it will update all rows in table A which is even slower than the original one
Upvotes: 0
Views: 51
Reputation: 11
You can use "with clause"
update A
SET A.a2 =
((with data as (SELECT B.b2, C.c2 FROM B JOIN C ON B.b1 = C.c1)
select *
from data_b_c)
select * from data b_c, A where A.a1 = b_c.b2 HAVING SUM(b_c.c2) > 1000)
Upvotes: 0
Reputation:
This kind of update is typically faster when done with a MERGE
instead:
MERGE INTO a
using
(
SELECT b.b2, 2 * SUM(C.c2) as c2_sum
FROM B
JOIN C ON B.b1 = C.c1
group by b.b2
HAVING SUM(C.c2) > 1000
) t on (t.b2 = a.a1)
when matched then update
set a2 = t.c2_sum;
Upvotes: 1
Reputation: 1997
If its really long operation you may create intermediate table where you calculate result. But you need to have primary key on both tables
Create table int_a as
SELECT 2* SUM(C.c2) as new_a2
, B.b2
FROM B JOIN C
ON B.b1 = C.c1 WHERE A.a1 = B.b2
GROUP BY B.b2
HAVING SUM(C.c2) > 1000
alter table int_a add constraint primary key pk_int_a (b2);
alter table a add constraint primary key pk_a (a2);
and update table a with join
update (select a2, int_a.new_a2
from A, INT_A
where A.a1 = INT_A.b2)
set a2 = new_a2
Upvotes: 0