CODEWITHSUNDEEP
sqlpostgresql
john mathew
john mathew

Reputation: 3

How can I update a column with new values in postgresql

I have a table called nyc which consist 3 columns , id , geom and area.Geom column is of type geometry. I want to calculate the area of each geom in the table and it to area column of nyc table.

I tried using the below command but it did not work:

update nyc as p set area=(select st_area(geom) from nyc) where p.id=nyc.id;

It give me an error saying:

LINE 1: update nyc as p set area=(select st_area(geom) from nyc...
                                       ^
HINT:  You will need to rewrite or cast the expression.

Since id is unique column I assume that I should update the column based on id. I am little confused how could I update the values in a row.Any help is appreciated.

Thanks

Upvotes: 0

Views: 599

Answers (2)

user330315
user330315

Reputation:

If id is the primary key, you don't need any sub-select:

update nyc
   set area = st_area(geom);

This will update all rows in the table, replacing the existing value of the area column with the new one.

Upvotes: 1

Rahul
Rahul

Reputation: 77866

You can re-write your UPDATE statement to something like below

update nyc as p 
set area = tab.new_area
from (
select id, st_area(geom) as new_area from nyc
) as tab 
where p.id = tab.id;

Upvotes: 1

Related Questions