Re-Serial the Line Numbers
Hello
i have table
( LN_NO smallint , TR_ty smallint , TR_NO int )
LN_NO TR_TY TR_No
1 12 700
2 12 700
3 12 700
4 12 700
Sometimes some rows deleted and i need to renumbering the LN_NO
For example ..the above example after delete the line number 3 would be as follow
LN_NO TR_TY TR_No
1 12 700
2 12 700
4 12 700
I need it like
LN_NO TR_TY TR_No
1 12 700
2 12 700
3 12 700
I tried to use
UPDATE mytable SET LN_NO= ROW_NUMBER() OVER ( ORDER BY LN_NO ASC ) .....
the error message come up
Windowed functions can only appear in the SELECT or ORDER BY clauses.
how can renumbering the LN_NO ?
i have table
( LN_NO smallint , TR_ty smallint , TR_NO int )
LN_NO TR_TY TR_No
1 12 700
2 12 700
3 12 700
4 12 700
Sometimes some rows deleted and i need to renumbering the LN_NO
For example ..the above example after delete the line number 3 would be as follow
LN_NO TR_TY TR_No
1 12 700
2 12 700
4 12 700
I need it like
LN_NO TR_TY TR_No
1 12 700
2 12 700
3 12 700
I tried to use
UPDATE mytable SET LN_NO= ROW_NUMBER() OVER ( ORDER BY LN_NO ASC ) .....
the error message come up
Windowed functions can only appear in the SELECT or ORDER BY clauses.
how can renumbering the LN_NO ?
Last Comment
The steps to take to make it easy are:
1. Construct a SELECT generating the data as needed. Make sure to include primary key values and the column to update in the result.
2. Update the SELECT.
Since you didn't provide enough info to go into details, I cannot post useful example code:
(a) Is LN_NO a primary key? If not, which columns are?
(b) Do you want to restart numbering with groups of data, like new TR_TY / TR_No pairs?
1. Construct a SELECT generating the data as needed. Make sure to include primary key values and the column to update in the result.
2. Update the SELECT.
Since you didn't provide enough info to go into details, I cannot post useful example code:
(a) Is LN_NO a primary key? If not, which columns are?
(b) Do you want to restart numbering with groups of data, like new TR_TY / TR_No pairs?
Windowed functions can only appear in the SELECT or ORDER BY clauses
You will have to use a derived table in order to achieve this.
You will have to use a derived table in order to achieve this.
ASKER
Hello Qlemo
Yes Exactly i need to restart numbering with groups of data, like new TR_TY / TR_No pairs
the primary key are LN_NO,TR_TY,TR_NO
Yes Exactly i need to restart numbering with groups of data, like new TR_TY / TR_No pairs
the primary key are LN_NO,TR_TY,TR_NO
update x
set ln_no = ln_no_new
from (
select ln_no_new=row_number() over (partition by tr_ty, tr_no order by ln_no),
ln_no, tr_ty, tr_no
from mytable) x
update mytable set ln_no = ln_no + 1e9
update -- like above
ASKER CERTIFIED SOLUTION
THIS SOLUTION IS ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
Why a "B"??
Good luck with future qs :-) .
Good luck with future qs :-) .
Microsoft SQL Server 2008
Microsoft SQL Server 2008 is a suite of relational database management system (RDBMS) products providing multi-user database access functionality.Component services include integration (SSIS), reporting (SSRS), analysis (SSAS), data quality, master data, T-SQL and performance tuning. Major improvements include the Always On technologies and support for unstructured data types.
50K
Questions
--
Followers
--
Top Experts
Get a personalized solution from industry experts
TRUSTED BY
update mytable
set @i = LN_NO = @i + 1