Search code examples
postgresqlcountsql-updatecorrelated-subquery

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

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.