[SQL Server] assign values to variables in update

-- Create a test environment
If object_id ('tb') is not null drop table TB
Go
Create Table TB
(
Fdate datetime,
Fperiod int,
Fnum int,
Fentryid int
)
Go
Insert Tb (fdate, fperiod, fnum)
Select '2014-1-1 ', 1, 1 Union all
Select '2014-1-1 ', 1, 1 Union all
Select '2014-1-1 ', 1, 1 Union all
Select '2014-1-1 ', 1, 1 Union all
Select '2014-1-3 ', 1, 2 Union all
Select '2014-1-3 ', 1, 2 Union all
Select '2014-1-3 ', 1, 2 Union all
Select '2014-1-4 ', 1, 4 union all
Select '2014-1-4 ', 1, 4
-- Query
-- If update can
Declare @ I int, @ fnum int
Set @ I = 0
Update TB
Set fentryid = @ I,
@ I = case when @ fnum = fnum then @ I + 1 else 0 end,
@ Fnum = fnum
Go
--
Select * from TB
/*
Fdate fperiod fnum fentryid
--------------------------------------------------------
00:00:00. 000 1 1 0
00:00:00. 000 1 1 1
00:00:00. 000 1 1 2
00:00:00. 000 1 1 3
00:00:00. 000 1 2 0
00:00:00. 000 1 2 1
00:00:00. 000 1 2 2
00:00:00. 000 1 4 0
00:00:00. 000 1 4 1

(9 rows affected)

*/

-- If you want to query
-- Because SQL2000 does not have the row_number function, a temporary table is used.
If object_id ('tempdb .. # t') is not null drop table # T
Go
Create Table # T
(
Fdate datetime,
Fperiod int,
Fnum int,
Fentryid int identity
)
Go
Insert # T
Select fdate, fperiod, fnum from TB order by fnum
Go
----
Select fdate, fperiod, fnum,
(Select count (1) from # t where fnum = A. fnum and fentryid <A. fentryid) fentryid
From # T

-- Result
/*
Fdate fperiod fnum fentryid
--------------------------------------------------------
00:00:00. 000 1 1 0
00:00:00. 000 1 1 1
00:00:00. 000 1 1 2
00:00:00. 000 1 1 3
00:00:00. 000 1 2 0
00:00:00. 000 1 2 1
00:00:00. 000 1 2 2
00:00:00. 000 1 4 0
00:00:00. 000 1 4 1

(9 rows affected)

*/