PostgreSQL update

Question

PostgreSQL 9.2 Platform

I am very new to PostgreSQL. I have this script that I was able to address in MSSQL, but the same approach does not work with Postgres.

I have this table

CREATE TABLE TEST(
ID INT,
Value1 INT,
Value2 INT
);

INSERT INTO TEST
VALUES
(1,10,0),
(2,20,0),
(3,50,0),
(4,100,0),
(5,500,0);

I need the total number in a column Value2e.g.

UPDATE TEST
SET Value2 = T2.Value1-T1.Value1
FROM TEST T1
INNER JOIN TEST T2
ON T2.ID=T1.ID+1;

SELECT * FROM TEST;

While this works fine in MSSQL, it does not work in Postgres. The command succeeds, but no rows are updated.

However, when I try to do this, I see that the logic is correct

SELECT T2.ID,T2.Value1-T1.Value1
FROM TEST T1
INNER JOIN TEST T2
ON T2.ID=T1.ID+1;

What am I doing wrong here?

SQLFIDDLE DEMO

Answer 1

First: unlike SQL Server, you do not repeat the target table when you want to update it based on the connection. Therefore, your update should look something like this:

UPDATE TEST t1
  SET Value2 = T2.Value1-T1.Value1
FROM TEST T2
where T2.ID = T1.ID+1;

( , )

Postgres , , , ( - , - ):

with summed as (
   select id, 
          sum(value1) over (order by id) as running_sum
   from test
)
update test 
  set value2 = running_sum
from summed
 where summed.id = test.id;

SQLFiddle: http://sqlfiddle.com/#!12/96a90/43

Answer 2

: http://www.postgresql.org/docs/9.1/static/sql-update.html

UPDATE TEST AS T1
SET Value2 = T2.Value1-T1.Value1
FROM TEST T2
WHERE T2.ID=(T1.ID+1);

http://sqlfiddle.com/#!12/96a90/40