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 ---------------------------(end of broadcast)--------------------------- TIP 6: Have you searched our list archives? http://archives.postgresql.org
