[FIXED] How to count specific value from multiple column in another table and update the results

Issue

I have table1 as following

a	b	c	d	e	f
10	23	29	33	37	40
9	13	21	25	32	42
11	16	19	21	27	31
14	27	30	31	40	42
16	24	29	40	41	42
14	15	26	27	40	42
2	9	16	25	26	40
8	19	25	34	37	39
2	4	16	17	36	39
9	25	30	33	41	44
1	7	36	37	41	42
2	11	21	25	39	45
22	23	25	37	38	42
2	6	12	31	33	40
3	4	16	30	31	37

And table2 as following

numbs	result
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20

I would like to update table2.result buy counting total matched numbs value from table1 column(a,b,c,d,e,f)

Have tried the below mentioned script but it's taking really long time to update fully so appreciate if somebody could provide me any alternative script which calculates faster.

UPDATE public.table2 AS t2 SET result = (select sum(
    (CASE WHEN t2.numbs=t1.a THEN 1 ELSE 0 END) +
    (CASE WHEN t2.numbs=t1.b THEN 1 ELSE 0 END) +
    (CASE WHEN t2.numbs=t1.c THEN 1 ELSE 0 END) +
    (CASE WHEN t2.numbs=t1.d THEN 1 ELSE 0 END) +
    (CASE WHEN t2.numbs=t1.e THEN 1 ELSE 0 END) +
    (CASE WHEN t2.numbs=t1.f THEN 1 ELSE 0 END) )     
                FROM public.table1 AS t1 )

Solution

It looks like there are no duplicate numbers in each row of table1(something like lottery numbers).

If my assumption is correct, you can simplify your code:

UPDATE table2 AS t2
SET result = (
  SELECT COUNT(*) 
  FROM table1 AS t1 
  WHERE t2.numbs IN (t1.a, t1.b, t1.c, t1.d, t1.e, t1.f)
); 

See the demo.

Answered By - forpas

Answer Checked By - Clifford M. (PHPFixing Volunteer)