CODEWITHSUNDEEP
sqlpostgresql
rcell
rcell

Reputation: 671

Update a table using aggregate of another table efficiently

I'm using

UPDATE main 
SET val = (
    SELECT SUM(..) 
    FROM other 
    WHERE main.id=other.main_id 
    GROUP BY main_id
)

However other has optional participation so when it runs, update seems to null out val when it is not found in the other table. Instead it should only update val for id that exist in the subquery.

How do I fix this?

Edit: I managed to fix it by adding

WHERE IN SELECT main_id FROM other

Upvotes: 1

Views: 50

Answers (2)

Clodoaldo Neto
Clodoaldo Neto

Reputation: 125254

update main 
set val = coalesce(
    select sum(..) 
    from other 
    where main.id=other.main_id 
    group by main_id
), val)

If the subquery returns null it will set valto val

Or better

update main 
set val = s.val_sum
from (
    select sum(..) as val_sum, main_id 
    from other 
    group by main_id
) s
where s.main_id = main.id

Upvotes: 2

jjanes
jjanes

Reputation: 44192

UPDATE main SET val = 
   (SELECT count(*) FROM other WHERE main.id=other.main_id)
WHERE exists (select 1 from other where main.id=other.main_id)

Upvotes: 0

Related Questions