Update in PostgreSQL

Question

Platform PostgreSQL 9.2

I am very new to PostgreSQL. I have this scenario, which I have been 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 a running total in the column Value2, like so

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 perfectly in MSSQL, it does not work in Postgres. The command executes successfully, but no rows get updated.

However, when I try this, I can 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

The command executes successfully, but nothing gets updated. Check out the linked SQLFiddle — Raj
– Raj, Commented Jul 4, 2014 at 6:00

score 3 · Accepted Answer · 2014-07-04 10:36:42Z

3

First: unlike SQL Server you do not repeat the target table when you want to update based on a join. So 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;

But this update, won't catch the last row (or the first one depending on how you join them)

But with Postgres this can be done using a window function, which is probably a bit faster (and - at least in my eyes - easier to read):

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

edited Jul 4, 2014 at 10:36

answered Jul 4, 2014 at 6:31

user330315

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

Comments

sn00k4h · Accepted Answer · 2014-07-04 06:30:26Z

3

Look at the documentation here: 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

answered Jul 4, 2014 at 6:30

sn00k4h

4434 silver badges10 bronze badges

Collectives™ on Stack Overflow

Update in PostgreSQL

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related