d the option ORDER for the update
command.
- Original Message -
From: "Greg Stark" <[EMAIL PROTECTED]>
To: "Viorel Dragomir" <[EMAIL PROTECTED]>
Cc: <[EMAIL PROTECTED]>
Sent: Tuesday, July 15, 2003 5:55 PM
Subject: Re: [SQL] summing tables
>
&g
- Original Message -
From: "Tom Lane" <[EMAIL PROTECTED]>
To: "Viorel Dragomir" <[EMAIL PROTECTED]>
Cc: <[EMAIL PROTECTED]>
Sent: Tuesday, July 15, 2003 6:42 PM
Subject: Re: [SQL] summing tables
> "Viorel Dragomir" <[EMAIL PROTECTE
"Viorel Dragomir" <[EMAIL PROTECTED]> writes:
> 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?
No, and *it does not matter*. You are forgetting that this all runs
under MVCC
"Viorel Dragomir" <[EMAIL PROTECTED]> writes:
> Anyway, in real life this update modifies only one row with a value wich is
> diff of null. It was really handy if it was specified the option ORDER for
> the update command.
Are you hoping to produce a running total? That's very difficult in stand
Thiele" <[EMAIL PROTECTED]>
Cc: <[EMAIL PROTECTED]>
Sent: Tuesday, July 15, 2003 6:14 PM
Subject: Re: [SQL] summing tables
> Erik Thiele <[EMAIL PROTECTED]> writes:
> > "Viorel Dragomir" <[EMAIL PROTECTED]> wrote:
> >> update table_name
>
Erik Thiele <[EMAIL PROTECTED]> writes:
> "Viorel Dragomir" <[EMAIL PROTECTED]> 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 th
Erik,
If you intent is to get a running total of a and b ordered by seq, you
should try this (assuming the table name is t):
update t set c = ( select sum(a) + sum(b) from t t1 where t1.seq <=
t.seq);
You should have an index on seq.
If the table is very large, it is going to be painfully slow.
To solve this problem efficiently you probably need the lead/lag analytic
functions. Unfortunately Postgres doesn't have them.
You could do it with something like:
update foo set c = a+b+(select c from foo as x where seq < foo.seq ORDER BY seq desc
LIMIT 1)
or the more standard but likely to
end loop;
return var2;
end; '
language 'plpgsql';
- Original Message -
From: "Erik Thiele" <[EMAIL PROTECTED]>
To: <[EMAIL PROTECTED]>
Sent: Tuesday, July 15, 2003 3:39 PM
Subject: Re: [SQL] summing tables
> On Tue, 15 Jul 2003 15:16:2
On Tue, 15 Jul 2003 15:16:21 +0300
"Viorel Dragomir" <[EMAIL PROTECTED]> 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 th
Erik Thiele wrote:
hi,
i have a table consisting of 4 integers.
seq is for making the table ordered. (ORDER BY SEQ ASC)
a,b,c maybe null
seq | a | b | c
-+++---
0 | 1 | 2 | 3
1 | 1 | 2 |
2 | 5 | 7 |
3 | -2 | -4 |
i am needing a sql statement to do
c=a+b+"the
hiele" <[EMAIL PROTECTED]>
To: <[EMAIL PROTECTED]>
Sent: Tuesday, July 15, 2003 3:09 PM
Subject: [SQL] summing tables
> hi,
>
> i have a table consisting of 4 integers.
>
> seq is for making the table ordered. (ORDER BY SEQ ASC)
> a,b
hi,
i have a table consisting of 4 integers.
seq is for making the table ordered. (ORDER BY SEQ ASC)
a,b,c maybe null
seq | a | b | c
-+++---
0 | 1 | 2 | 3
1 | 1 | 2 |
2 | 5 | 7 |
3 | -2 | -4 |
i am needing a sql statement to do
c=a+b+"the c of the row
13 matches
Mail list logo
