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 )
CodePudding user response：
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.
CodePudding user response：
OK, so it can be done. The fact that this is so ugly is an indication that your database is not properly designed. If the numbers can be handled independently, then they should be in separate rows.
DROP TABLE numbers;
CREATE TABLE numbers (
a int,
b int,
c int,
d int,
e int,
f int
);
INSERT INTO numbers VALUES
(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)
;
DROP TABLE table2;
CREATE TABLE table2 (
numbs int,
result int
);
INSERT INTO table2 VALUES
(1,0), (2,0), (3,0), (4,0), (5,0), (6,0), (7,0), (8,0),
(9,0), (10,0), (11,0), (12,0), (13,0), (14,0), (15,0),
(16,0), (17,0), (17,0), (18,0), (19,0), (20,0), (21,0),
(22,0), (23,0), (24,0);
UPDATE table2 SET result=0;
UPDATE table2 SET result = result n.cnt
FROM (SELECT a,count(*) cnt FROM numbers GROUP BY a) n
WHERE table2.numbs = n.a;
UPDATE table2 SET result = result n.cnt
FROM (SELECT b,count(*) cnt FROM numbers GROUP BY b) n
WHERE table2.numbs = n.b;
UPDATE table2 SET result = result n.cnt
FROM (SELECT c,count(*) cnt FROM numbers GROUP BY c) n
WHERE table2.numbs = n.c;
UPDATE table2 SET result = result n.cnt
FROM (SELECT d,count(*) cnt FROM numbers GROUP BY d) n
WHERE table2.numbs = n.d;
UPDATE table2 SET result = result n.cnt
FROM (SELECT e,count(*) cnt FROM numbers GROUP BY e) n
WHERE table2.numbs = n.e;
UPDATE table2 SET result = result n.cnt
FROM (SELECT f,count(*) cnt FROM numbers GROUP BY f) n
WHERE table2.numbs = n.f;
Output:
sqlite> .mode box
sqlite> select * from table2;
┌───────┬────────┐
│ numbs │ result │
├───────┼────────┤
│ 1 │ 1 │
│ 2 │ 4 │
│ 3 │ 1 │
│ 4 │ 2 │
│ 5 │ 0 │
│ 6 │ 1 │
│ 7 │ 1 │
│ 8 │ 1 │
│ 9 │ 3 │
│ 10 │ 1 │
│ 11 │ 2 │
│ 12 │ 1 │
│ 13 │ 1 │
│ 14 │ 2 │
│ 15 │ 1 │
│ 16 │ 5 │
│ 17 │ 1 │
│ 17 │ 1 │
│ 18 │ 0 │
│ 19 │ 2 │
│ 20 │ 0 │
│ 21 │ 3 │
│ 22 │ 1 │
│ 23 │ 2 │
│ 24 │ 1 │
└───────┴────────┘
sqlite>
CodePudding user response：
here is one way :
update table2
set result = (
select sum(case when numbs in (a,b,c,d,e,f) then 1 else 0 end)
from numbers
);
db<>fiddle here