0

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?

1 Answer 1

2

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;
3
  • "set counter = src.counter" may be should be "set counter = src.counter+dst.counter" right? I need the sum of counters
    – Pumych
    Aug 8, 2020 at 16:04
  • 1
    Yes, if you want to add the values you need +
    – user1822
    Aug 8, 2020 at 16:10
  • What will be the answer if I have key of two columns user_id and group_id
    – Pumych
    Aug 22, 2020 at 10:44

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.