Avatar of Ali Saad
Ali SaadFlag for Kuwait asked on

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 ?
Microsoft SQL Server 2008

Avatar of undefined
Last Comment
Scott Pletcher

8/22/2022 - Mon
TheAvenger

declare @i smallint = 0

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

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
ASKER
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
Qlemo

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

ASKER CERTIFIED SOLUTION
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
or
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.