postgresql update query with inner join

Question

when I execute the following update query in postgresql, it updates every row to the same value.What is wrong with the query?

update survey_library
set companies_for_survey = v.companies_for_survey
from survey_library lib
inner join (SELECT sl.original_row_id as template_id, 
        array_agg(DISTINCT resp_t.id) as companies_for_survey
         FROM public.survey_library sl
            inner join survey_storage ss 
                on ss.template_id = sl.id
                and ss.status in ('Ready to Launch','Launching on Start Date','In Progress',
                                  'Past  Due')
            inner join company_by_path cbp 
                on ss.company_by_path_id = cbp.id
            inner join tenant resp_t 
                on cbp.owner_tenant_id = resp_t.id
            GROUP BY sl.original_row_id) v
    on lib.id = v.template_id;

Whereas following query works fine.

select lib.id, v.companies_for_survey
from survey_library lib
inner join (SELECT sl.original_row_id as template_id, 
        array_agg(DISTINCT resp_t.id) as companies_for_survey
         FROM public.survey_library sl
            inner join survey_storage ss 
                on ss.template_id = sl.id
                and ss.status in ('Ready to Launch','Launching on Start Date','In Progress',
                                  'Past Due')
            inner join company_by_path cbp 
                on ss.company_by_path_id = cbp.id
            inner join tenant resp_t 
                on cbp.owner_tenant_id = resp_t.id
            GROUP BY sl.original_row_id) v
 on lib.id = v.template_id

This query gives result like:

id   |companies_for_survey 
1    |{324,921,1045,1199,1360,1379,1385,1398,1401,1415,1570,1589,1750,2167,2199,2581,2680,3359,4085,4333,4535,7289,7938,10740,10819,11017,11105,11932,13569,14842,34992,36656,37408,41963,41991,41995,43205,43344,43353,43371,43373,43374,43384,43402,43415,43440,434|
3|{38,43,63,77,78,79,93,94,96,97,99,101,105,108,109,111,116,117,118,124,125,128,130,131,132,146,149,179,230,262,270,273,274,275,276,277,279,285,286,292,293,295,297,298,299,300,301,302,303,304,305,306,307,308,311,312,315,316,317,318,320,321,322,323,325,326,3|
4|{79,86,90,91,93,96,99,118,121,125,128,130,131,247,257,262,272,273,276,290,298,300,301,305,306,308,310,311,312,316,317,320,324,325,326,327,328,329,330,331,357,368,375,387,400,414,427,429,437,440,450,453,455,462,473,476,486,502,505,516,518,530,531,555,589,6|

Gordon Linoff · Accepted Answer · 2020-08-10 02:00:38Z

You are close:

update survey_library lib
    set companies_for_survey = v.companies_for_survey
from (SELECT sl.original_row_id as template_id, 
        array_agg(DISTINCT resp_t.id) as companies_for_survey
         FROM public.survey_library sl
            inner join survey_storage ss 
                on ss.template_id = sl.id
                and ss.status in ('Ready to Launch','Launching on Start Date','In Progress',
                                  'Past  Due')
            inner join company_by_path cbp 
                on ss.company_by_path_id = cbp.id
            inner join tenant resp_t 
                on cbp.owner_tenant_id = resp_t.id
            GROUP BY sl.original_row_id) v
    where lib.id = v.template_id;

In Postgres, you do not repeat the table being updated in the FROM clause. If you do, that is another reference to the table and treated like a CROSS JOIN.

Collectives™ on Stack Overflow

postgresql update query with inner join

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related