troubleshooting Question

Re-Serial the Line Numbers

Avatar of ali_alannah
ali_alannah asked on
Microsoft SQL Server 2008
7 Comments1 Solution345 ViewsLast Modified:
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 ?
Join the community to see this answer!
Join our exclusive community to see this answer & millions of others.
Unlock 1 Answer and 7 Comments.
Join the Community
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 1 Answer and 7 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros