Avatar of Ali Saad
Ali SaadFlag for Kuwait asked on

Re-Serial the Line Numbers

i have table
 ( LN_NO smallint , TR_ty smallint , TR_NO int )

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
1            12        700
2            12        700
4            12       700

I need it like
1            12        700
2            12        700
3            12       700

I tried to use

the error message come up
Windowed functions can only appear in the SELECT or ORDER BY clauses.

how can renumbering the LN_NO ?
Microsoft SQL Server 2008

Avatar of undefined
Last Comment
Scott Pletcher

8/22/2022 - Mon

declare @i smallint = 0

update mytable
set @i = LN_NO = @i + 1

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?
Anthony Perkins

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.
Experts Exchange has (a) saved my job multiple times, (b) saved me hours, days, and even weeks of work, and often (c) makes me look like a superhero! This place is MAGIC!
Walt Forbes
Ali Saad

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

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

Open in new window

But changing a primary key is usually no good idea. Plus, the above might not work as constraint checking might prevent the renumbering, in which case we would have to do the numbering twice, like with
update mytable set ln_no = ln_no + 1e9
update -- like above

Open in new window

Scott Pletcher

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
See how we're fighting big data
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
Scott Pletcher

Why a "B"??

Good luck with future qs :-) .
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.