Postgresql – Update table with sum from other table for the same id

postgresql

Given two tables:

Table src

user_id | counter | text
------------------------
5       | 1       | aa
7       | 7       | bb

Table dst

user_id | counter | text
------------------|-----
3       | 5       | ee
7       | 6       | ff

I need to do the following:

If row with user_id exists in src table but not exists in dst table – copy it from src to dst

If row with same user_id exists in both tables, then update the counter in dst with sum of counter from src and counter from dst, and overwrite the text

Final dst should be like that

Table dst

user_id | counter | text
------------------------
3       | 5       | ee
5       | 1       | aa
7       | 13      | bb

I started to insert the rows with missing user_id:

insert into dst 
select * 
from src as s 
where s.user_id not in (select d.user_id from dst as d);

How do I do the update for the exist user_id values?

UPDATE:

What will be the answer in case I have key of two columns user_id and group_id?

Best Answer

this should do what you want

update dst
   set counter = dst.counter + src.counter
from src
where src.user_id = dst._user_id;

If dst.user_id is the primary key (or defined as unique), you can combine both into one statement:

insert into dst (user_id, counter, text)
select user_id, counter, text
from src 
on conflict (user_id) 
do update
  set counter = dst.counter + excluded.counter;
Related Question