I have two tables let’s say table1 is as below (no duplicates in table)

and tbale2 is as following

and would like to update table1.result with total matches of "joins" after comparing them into table2 (within columns A:CC).

For instance, the below mentioned code provides total of "a,c,f,i" from table2 (works only as per a single criteria), but I want the entire table1.result to be updated please.

thanks in advance

SELECT a,c,f,i,
count (*) FROM table2 GROUP BY
a,c,f,i
HAVING count(*) >=1 order by count desc

Maybe there is a way to amend the below mentioned code;

UPDATE table1 SET table1.result = tbc.count
FROM (SELECT "concat(same as joins from table1)"
             count(*) AS count
      FROM table2
      GROUP BY "concat(same as joins from table1)") AS tbc
WHERE table1.joins = tbc."concat(same as joins from table1)"

CodePudding user response：

As mentioned in the comment: you can use plpgsql dynamic commands to construct your queries:

do $$ 
declare 
  rec record;
  current_joins text;
  current_result int;
begin
  for rec in select joins from table1 loop
     select rec.joins into current_joins;
     execute format('select count(*) 
                     from (
                        select 1
                        from table2 
                        group by %1$s 
                        having count(*)>=1 
                     ) as some_alias;',
                     current_joins) 
              into current_result;
     update table1 set result=current_result where joins=current_joins;
  end loop;
end $$;

Which simply loops over your "joins" and executes your query once for each of them, updating the corresponding "result" field:

select * from table1;
   joins   | result
----------- --------
 a,b       |      7
 a,c,f,i   |      9
 a,d,o,x,y |     11
 a,e       |      7
 a,f       |      9
 a,g,x     |     10
 a,h       |     10
 a,i,l,r,s |     10
 a,j       |      9
 a,k       |      9

Online demo. Above is to show the principle - I don't expect you actually want one random "count" as your result. It'd help if you explained your logic a bit more, maybe adding an clarified example result.