Indeed it was a mistake not to put the table_name. in where clause.
But this doesn't resolve the problem.
Do you know in which order the update will modify the rows?
My lucky guess is that it takes from last inserted rows to the first row. In
this way only one row gets updated correctly.
I add the result from my unfortunate solution, even corrected.
select * from table_name;seq | a | b | c
-----+---+---+--- 1 | 1 | 2 | 3 2 | 5 | 9 | 3 | 1 | 2 | 4 | 4 | 7 | 5 | 4 | 2 | 6 | 0 | 1 |
(6 rows)
update table_nameset c = a + b + (select c from table_name as x where seq =
table_name.seq-1)where c is null;
select * from table_name;seq | a | b | c
-----+---+---+---- 1 | 1 | 2 | 3 2 | 5 | 9 | 17 3 | 1 | 2 | 4 | 4 | 7 | 5 | 4 | 2 | 6 | 0 | 1 |
----- Original Message -----
From: "Tom Lane" <tgl@sss.pgh.pa.us>
To: "Erik Thiele" <erik@thiele-hydraulik.de>
Cc: <pgsql-sql@postgresql.org>
Sent: Tuesday, July 15, 2003 6:14 PM
Subject: Re: [SQL] summing tables
> Erik Thiele <erik@thiele-hydraulik.de> writes:
> > "Viorel Dragomir" <bigchief@vio.ro> wrote:
> >> update table_name
> >> set c = a + b + (select c from table_name as x where x.seq = seq-1)
> >> where c is null;
>
> > hmmm. the query is run row by row, isn't it?
> > but it will have different results depending on the order of those rows.
>
> No, it won't, because the SELECTs will not see the changes from the
> not-yet-completed UPDATE. The above command is almost right; it needs
> to be
>
> update table_name
> set c = a + b + (select c from table_name as x where seq =
table_name.seq-1)
> where c is null;
>
> because inside the sub-SELECT, unadorned "seq" will refer to the SELECT's
> table.
>
> You didn't say exactly what you wanted to do with null inputs, so that
> issue may need more thought.
>
> regards, tom lane
>
> ---------------------------(end of broadcast)---------------------------
> TIP 5: Have you checked our extensive FAQ?
>
> http://www.postgresql.org/docs/faqs/FAQ.html
