Q:

pgsql how to count values from two columns

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"

A:

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);  

DEMO

