I have 2 tables
First Table:
num | job1 | job2
--------------------
1 14 12
2 23 14
3 3 12
4 21 3
5 6 8
second:
id | jobs
------------
3
12
14
21
23
etc...
I need to count how many times second table's id appears at first table,s
columns job1 and job2 and update that total value to second table column "jobs"
You need to first un-pivot the first table, so that you can group on the job ids:
select t.job_id, count(*) as num_jobs
from first_table, unnest(array[job1, job2]) as t(job_id)
group by t.job_id;
With your sample data that returns:
job_id | num_jobs
-------+---------
21 | 1
6 | 1
12 | 2
23 | 1
14 | 2
3 | 2
8 | 1
Now this can be used to update the second_table:
update second_table
set jobs = x.num_jobs
from (
select t.job_id, count(*) as num_jobs
from first_table, unnest(array[job1, job2]) as t(job_id)
group by t.job_id
) x
where x.job_id = id;
You could use a correlated update.
UPDATE table2 t2
SET jobs = (SELECT Count(*) AS job_count
FROM (SELECT job1 id
FROM table1
UNION ALL
SELECT job2 id
FROM table1) t1
WHERE t1.id = t2.id
GROUP BY id);
And yet another solution:
update table2 set
jobs = (
select sum(case when job1 = job2 then 2 else 1 end)
from table1
where table2.id = job1 or table2.id = job2)
