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 ?
ali_alannahAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

TheAvengerCommented:
declare @i smallint = 0

update mytable
set @i = LN_NO = @i + 1
0
QlemoBatchelor, Developer and EE Topic AdvisorCommented:
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?
0
Anthony PerkinsCommented:
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.
0
Newly released Acronis True Image 2019

In announcing the release of the 15th Anniversary Edition of Acronis True Image 2019, the company revealed that its artificial intelligence-based anti-ransomware technology – stopped more than 200,000 ransomware attacks on 150,000 customers last year.

ali_alannahAuthor Commented:
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
0
QlemoBatchelor, Developer and EE Topic AdvisorCommented:
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

0
Scott PletcherSenior DBACommented:
UPDATE tn
SET LN_NO = tn_row_nums.row_num
FROM dbo.tablename tn
INNER JOIN (
    SELECT
        LN_NO, ROW_NUMBER() OVER (ORDER BY LN_NO) AS row_num
    FROM dbo.tablename
) AS tn_row_nums ON
    tn_row_nums.LN_NO <> tn_row_nums.row_num AND
    tn_row_nums.LN_NO = tn.LN_NO


For example:


USE tempdb

create table dbo.tablename (
    LN_NO int,
    TR_TY int,
    TR_No int
    )
insert into dbo.tablename
select 1,            12   ,     701 UNION ALL
select 2 ,           12  ,      702 UNION ALL
select 3  ,          12 ,      703 UNION ALL
select 4   ,         12,       704 UNION ALL
select 5   ,         12,       705 UNION ALL
select 6   ,         12,       706

DELETE FROM dbo.tablename
WHERE LN_NO IN ( 3, 5 )

SELECT *
FROM dbo.tablename
ORDER BY LN_NO

UPDATE tn
SET LN_NO = tn_row_nums.row_num
FROM dbo.tablename tn
INNER JOIN (
    SELECT
        LN_NO, ROW_NUMBER() OVER (ORDER BY LN_NO) AS row_num
    FROM dbo.tablename
) AS tn_row_nums ON
    tn_row_nums.LN_NO <> tn_row_nums.row_num AND
    tn_row_nums.LN_NO = tn.LN_NO

SELECT *
FROM dbo.tablename
ORDER BY LN_NO

drop table dbo.tablename
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Scott PletcherSenior DBACommented:
Why a "B"??

Good luck with future qs :-) .
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2008

From novice to tech pro — start learning today.