How can I use the values of my table in postgres sequentially?-CodePudding

I'm trying to update a table so that the values of one column that correspond to a certain value in another column = 1. Here is my code so far:

update myTable
set area = area * 
    (1/ (select sum(area)
         from myTable
         where census_tract = 36081116301))
where census_tract = 36081116301;

Here I'm updating per census tract. Multiple values in the area column are attributed to the same census tract. How can I do this for each and every census tract without calling them all individually? Thank you!

CodePudding user response：

You seem to want a correlated subquery:

update myTable
    set area = area *
               (select 1.0 / sum(t2.area)
                from myTable t2
                where t2.census_tract = t.census_tract
               );

However, this would often be handled using an aggregation query instead:

update myTable t
    set area = area / c.total_area
    from (select census_tract, sum(area) as total_area
          from myTable 
          group by census_tract
         ) c
    where c.census_tract = t.census_tract;

I would also suggest that you store the area proportion in another column. It seems dangerous to write over the area data that is already there.