The primary problem was that the update command doesn't modify rows in the order u want to do it. I think the update starts with the latest inserted rows. I guess.
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. ----- 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 > > 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 be way slower: > > update foo set c = a+b+(select c from foo as x where seq = (select max(seq) from foo as y where seq < foo.seq)) > > > However, i would suggest that if you have an implicit relationship between > records you should make that relationship explicit with a foreign key. If you > had a column that contained the seq of the parent record then this would be > easy. I'm really puzzled how this query as currently specified could be > useful. > > > -- > greg > > > ---------------------------(end of broadcast)--------------------------- > TIP 4: Don't 'kill -9' the postmaster ---------------------------(end of broadcast)--------------------------- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
