On Thursday 06 Feb 2003 10:10 am, betty wrote:
> Hi..
>
> I have table xx:
> id debet credit balance
> 1 1000 0 0
> 2 2000 0 0
> 3 0 2500 0
> 4 0 100 0
>
> command in ms-sql 7 can use calculate field (column) balance from id=1 to
> id=4:
> "update xx set bal=balance=bal+debet-credit"
> result:
> id debet credit balance
> 1 1000 0 1000
> 2 2000 0 3000
> 3 0 2500 500
> 4 0 100 400
>
> How command sql can use in psotgresql 7.3?
Three options:
1. Write a function to update the records procedurally (see the plpgsql
section of the manuals). I'd probably use a cursor for this.
2. Write a trigger to keep entries up to date as you insert/delete/update
entries (basically, same as above but keeps things up to date)
3. Use a sub-query
richardh=> SELECT * FROM bank;id | credit | debit | balance
----+--------+-------+--------- 1 | 1000 | 0 | 0 2 | 0 | 250 | 0 3 | 2000 | 0 | 0
(3 rows)
richardh=> UPDATE bank SET balance = (SELECT sum(credit)-sum(debit) AS newbal
FROM bank b2 WHERE b2.id<=bank.id);
UPDATE 3
richardh=> SELECT * FROM bank;id | credit | debit | balance
----+--------+-------+--------- 1 | 1000 | 0 | 1000 2 | 0 | 250 | 750 3 | 2000 | 0 | 2750
(3 rows)
Note that for lots of rows, this may be slow. You might want to rewrite it in
the form b2.balance+b2.credit-b2.debit where b2.id=bank.id-1 but this
requires "id" to not have any gaps in the sequence and also needs special
logic to handle id=1 (or whatever the smallest "id" is).
HTH
-- Richard Huxton