May 4, 2008 at 10:48 pm
Hi
I have a sum value field (dig1) that contains a maximum value of 65535.
Once this upper limit is reached the field is reset back to 0 and the count begins again. This happens a number of times within the table.
I want to write an update query that removes this reset affect so that the values continue to rise.
eg: 65350, 65450, 15, 115 becomes 65350, 65450, 65550, 65650
The components of the query as far as i can tell are:
a)Order by rowid
b)Where dig1 < dig1 of previous record add 66535
c)Loop until there are no dig1 values that are less than the dig1 value of the previous record.
My SQL is limited, any help would be much appreciated. I have attached a sample table.
May 5, 2008 at 2:01 am
OK
I've gotton this far which updates the each row where digi1 value is less than the previous digi1 value.
update mda320_results2
set digi1=mda320_results2.digi1+65535
from mda320_results as mda320_results1 left join mda320_results as mda320_results2
on mda320_results2.RowID = mda320_results1.RowID + 1
where mda320_results2.digi1 < mda320_results1.digi1
Can anyone advise how to make this query loop until
mda320_results2.digi1 < mda320_results1.digi1
does not return a value?
May 5, 2008 at 7:34 am
select 1
while @@rowcount > 0
... your update script here ...
Or you can use "While exists ()". Put the query you want inside the parentheses.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
May 7, 2008 at 11:21 pm
Thanks, that looped nicely:
select * from mda320_results as mda320_results1 left join mda320_results as mda320_results2
on mda320_results2.counter = mda320_results1.counter + 1
where mda320_results2.digi1 < mda320_results1.digi1
while @@rowcount > 0
then my update query
Viewing 4 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply