Query to update rowNum
- by BrokeMyLegBiking
Can anyone help me write this query more efficiently?
I have a table that captures TCP traffic, and I'd like to update a column called RowNumForFlow which is simly the sequential number of the IP packet in that flow. The code below works fine, but it is slow.
declare @FlowID int
declare @LastRowNumInFlow int
declare @counter1 int
set @counter1 = 0
while (@counter1 < 1)
BEGIN
set @counter1 = @counter1 + 1
-- 1)
select top 1
@FlowID = t.FlowID
from Traffic t
where t.RowNumInFlow is null
if (@FlowID is null)
break
-- 2)
set @LastRowNumInFlow = null
select top 1
@LastRowNumInFlow = RowNumInFlow
from Traffic
where FlowID=@FlowID and RowNumInFlow is not null
order by ID desc
if @LastRowNumInFlow is null
set @LastRowNumInFlow = 1
else
set @LastRowNumInFlow = @LastRowNumInFlow + 1
update Traffic set RowNumInFlow = @LastRowNumInFlow
where ID = (select top 1 ID from Traffic where
flowid = @FlowID and RowNumInFlow is null)
END
Example table values after query has run:
ID FlowID RowNumInFlow
448923 44 1
448924 44 2
448988 44 3
448989 44 4
448990 44 5
448991 44 6
448992 44 7
448993 44 8
448995 44 9
448996 44 10
449065 44 11
449063 45 1
449170 45 2
449171 45 3
449172 45 4
449187 45 5
