Update table after counting matches from another table-CodePudding

I am confused how to write this query

I have 2 tables where the data in table1 needs to be updated based on the results from table2

For instance, if t1.a is available in either t2.a, t2.b, t2.c, t2.d, t2.e then the result should be 1, but based on the below formula I'm getting sum of the available matches from t2

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

Basically my requirement is if the result of t1.a>0 then 1 else 0 if t1.b>0 then 1 else 0 and so on..

I would prefer to use the query as following where i have added >0 but isn't working;

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

CodePudding user response：

Based on your problem definition:

create table table1 (id integer, a integer, result integer);
create table table2 (id integer, a integer, b integer, c integer, d integer, e integer);
insert into table1 values (1, 3, 0);
insert into table2 values (1, 9,2,5,4,3);
insert into table1 values (1, 8, 0);

select case when coalesce(t1.a in(t2.a, t2.b, t2.c, t2.d, t2.e), 'f') then 1 else 0 end from table1 as t1, table2 t2;
case 
------
    1
    0
update  table1 as t1 set result = (select case when coalesce(t1.a in(t2.a, t2.b, t2.c, t2.d, t2.e), 'f') then 1 else 0 end from table2 t2);
select * from table1;
 id | a | result 
---- --- --------
  1 | 3 |      1
  1 | 8 |      0