I have table1 as following;

And table2 as following;

and would like to update table1.status by finding how many numbers from table1 is matched/available in table2

For instance, based on the above scenarios, the result will be 2 (as mentioned below) because there are only two numbers 7 & 21 in table2;

appreciate if somebody could provide me a query

CodePudding user response：

As commented above, this data model and query request makes no sense.

Use jsonb to pivot columns to rows:

with targets as (
  select distinct v 
    from table2 t
         cross join lateral jsonb_each(to_jsonb(t)) as e(k, v)
), sourcequery as (
  select distinct a, b, c, d, e, v
    from table1 t
         cross join lateral jsonb_each(to_jsonb(t) - 'status') as e(k, v)
), matches as (
  select s.a, s.b, s.c, s.d, s.e, count(t.v) as status
    from sourcequery s
         left join targets t on t.v = s.v
   group by s.a, s.b, s.c, s.d, s.e
)
update table1
   set status = m.status
  from matches m 
 where m.a = table1.a
   and m.b = table1.b
   and m.c = table1.c
   and m.d = table1.d
   and m.e = table1.e;

db<>fiddle here

CodePudding user response：

Agree with previous posters: this this is a terrible data model. But you can get what you are asking for by expanding the columns from table2 into individual rows then determining the distinct values contain as column value in table 1. Finally, count the results. (see demo)

select sq.a,sq.b,sq.c,sq.d,sq.e,count(*) status 
from (
       select distinct on (v) t1.*,v
         from t1
        cross join 
              ( select a v from t2 union all
                select b   from t2 union all
                select c   from t2 union all
                select d   from t2 union all
                select e   from t2  
              ) cju 
          where cju.v in (t1.a,t1.b,t1.c,t1.d,t1.e)
      ) sq 
group by sq.a,sq.b,sq.c,sq.d,sq.e;