Thread: conversi ms-sql7 vs postgresql 7.3
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? thank's a lot betty.
> > 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? > Try UPDATE xx SET balance=balance+debet-credit ; Regards, Christoph
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
hi Christoph,
your command :
"UPDATE xx SET balance=balance+debet-credit "
result calculate balance for each row, ex:id debet credit balance1 1000 0
10002 2000 0 20003 0 2500 -25004 0 100
-100
not result calculate balance, ex:
id debet credit balance1 1000 0 10002 2000 0 30003
0 2500 5004 0 100 400
regards
betty
"Christoph Haller" <ch@rodos.fzk.de> wrote in message
news:3E438396.2983BD2B@rodos.fzk.de...
> >
> > 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?
> >
> Try
> UPDATE xx SET balance=balance+debet-credit ;
>
> Regards, Christoph
>
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 5: Have you checked our extensive FAQ?
>
> http://www.postgresql.org/users-lounge/docs/faq.html
-----BEGIN PGP SIGNED MESSAGE----- Hash: SHA1 > 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" You cannot do such a thing in SQL alone: you must use a procedural language. One way is with plpgsql: CREATE OR REPLACE FUNCTION makebalance() RETURNS VARCHAR LANGUAGE 'plpgsql' AS ' DECLARE total INTEGER := 0; myrow RECORD; BEGIN FOR myrow IN SELECT id, credit-debit AS cd FROM xx ORDER BY id ASC LOOP total := total + myrow.cd; UPDATE xx SET balance= total WHERE id=myrow.id; END LOOP; RETURN ''Final balance: '' || total; END; '; This is just a rough idea. You may have to create the language first: see the "createlang" script in the bin directory of your PostgreSQL installation. - -- Greg Sabino Mullane greg@turnstep.com PGP Key: 0x14964AC8 200302101601 -----BEGIN PGP SIGNATURE----- Comment: http://www.turnstep.com/pgp.html iD8DBQE+SBN9vJuQZxSWSsgRAoNEAJ9IKmRW6IlHu12x2w7i0G6THB7oGACgi7lM bIyssS1GQRgY0aQFRzyQKl8= =ArPb -----END PGP SIGNATURE-----
Greg, > > 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" > > You cannot do such a thing in SQL alone: you must use a procedural > language. One way is with plpgsql: You're mistaken, I think (I can't find the original e-mail in this thread) UPDATE totals SET balance = total_bal FROM (SELECT acct_id, (sum(credit) - sum(debit)) as total_balFROM accounts GROUP BY acct_id) tb WHERE tb.acct_id = totals.acct_idAND totals.acct_id = $selection ... would update a ficticious "totals" table with the sum of credits and debits for a particular account. -- -Josh BerkusAglio Database SolutionsSan Francisco