From: Viorel Dragomir <bigchief@vio.ro>
To: pgsql-sql@postgresql.org
Subject: Re: summing tables
Date: Tue, 15 Jul 2003 18:32:07 +0300
Message-ID: <012b01c34ae6$40ab0870$0600a8c0@fix.ro> (raw)
References: <20030715140921.7c5a6a6c.erik@thiele-hydraulik.de>
<001501c34aca$e70ca280$0600a8c0@fix.ro>
<20030715143925.54716ed4.erik@thiele-hydraulik.de>
<22671.1058282096@sss.pgh.pa.us>
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_name
set 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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: bigchief@vio.ro
Subject: Re: summing tables
In-Reply-To: <012b01c34ae6$40ab0870$0600a8c0@fix.ro>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox