update count from a table in another

Question

I am new to postgres so sry if this sound like a stupid question

I have a polygons shp in postgresql named t1 with fields like this

id  |  area  |  grid_id
1   |  10    |  01_01
2   |  100   |  01_02
3   |  1000  |  01_03
4   |  10    |  01_01

I want to count the poligons with a certain unique "grid_id" which I can make with

SELECT COUNT (*) from public.t1 group by grid_id

Now I want to update another table, t2, in a field "nr_polig" with the number of polygons corresponding to each unique grid_id

id  |  name  |  nr_polig
1   |  01_01 |  
2   |  01_02 |  
3   |  01_03 |  
4   |  01_04 |

I ve tried

Update public.t2 set nr_polig = (
    SELECT COUNT(*)  from public.t1 group by grid_id where public.grid_id = grid_id
)

but its updating the total number of polygons

later_edit

it worked like this

UPDATE public.t2 
  set nr_polig = (SELECT COUNT (*) from public.t1 where public.t1.grid_id = public.t2.name)

Laurenz Albe · Accepted Answer · 2019-12-17 11:20:57Z

3

You can join the tables:

UPDATE public.t2
SET nr_polig = s.cnt
FROM (SELECT grid_id, count(*) AS cnt
      FROM public.t1
      GROUP BY grid_id) AS s
WHERE s.grid_id = t2.grid_id;

answered Dec 17, 2019 at 11:20

Laurenz Albe

257k22 gold badges313 silver badges389 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

GMB · Accepted Answer · 2019-12-17 11:44:39Z

1

You need to properly correlate the subquery by prefixing column name with the table it belongs to. Also the group by clause is not needed in the subquery.

update t2 set nr_polig = (
    select count(*)  
    from t1
    where t1.grid_id = t2.name
)

edited Dec 17, 2019 at 11:44

answered Dec 17, 2019 at 11:17

GMB

224k25 gold badges103 silver badges151 bronze badges

Collectives™ on Stack Overflow

update count from a table in another

but its updating the total number of polygons

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

but its updating the total number of polygons

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related