Iterate through table, perform calculation on each row

user_6031805 0
Viewed 47112

I would like to preface this by saying I am VERY new to SQL, but my work now requires that I work in it.

I have a dataset containing topographical point data (x,y,z). I am trying to build a KNN model based on this data. For every point 'P', I search for the 100 points in the data set nearest P (nearest meaning geographically nearest). I then average the values of these points (this average is known as a residual), and add this value to the table in the 'resid' column.

As a proof of concept, I am trying to simply iterate over the table, and set the value of the 'resid' column to 1.0 in every row.

My query is this:

CREATE OR REPLACE FUNCTION LoopThroughTable() RETURNS VOID AS '
DECLARE row table%rowtype;
BEGIN
    FOR row in SELECT * FROM table LOOP
        SET row.resid = 1.0;
    END LOOP;
END

' LANGUAGE 'plpgsql';

SELECT LoopThroughTable() as output;

This code executes and returns successfully, but when I check the table, no alterations have been made. What is my error?

postgis postgresql sql
4 Answers

Following is a simple example to update rows in a table:

Assuming the row id field id

Update all rows:

UPDATE my_table SET field1='some value'
WHERE id IN (SELECT id FROM staff)

Selective row update

UPDATE my_table SET field1='some value'
WHERE id IN (SELECT id FROM staff WHERE field2='same value')
user_8365510
Related