[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 361
  • Last Modified:

SQL Stored Procedure to resequence pointer field

Experts,

I am writing an application in VB.Net 2008 which uses SQL Server 2005.  The particular database I Am working with now holds Stock records which has a Primary Key and a Sequence number.  The user may add multiple records and may also remove records from the database which will create gaps in the Sequence.

Since there will or could be gaps I wanted to call a Stored Procedure to re_swquence the records.  I thought I could use a WHILE loop with a variable, but the UPDATE only updates the last record.  Could you show me a Stored Procedure that coulld accomplish this.
0
Peter Allen
Asked:
Peter Allen
  • 2
  • 2
1 Solution
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
>which will create gaps in the Sequence.
why do you want to avoid the gaps. after all, it's just a number to identify the records ...

I would not try to do this on "stock" database.
0
 
kamindaCommented:
You can do something like this to repopulate the sequence numbers from the beginning

UPDAET yt
SEQ_Column =
 row_number() OVER (ORDER BY SEQ_Column) FROM YourTABLE yt
0
 
Peter AllenAuthor Commented:
I tried this code but received the error, "Windowed function can only appear in SELECT and ORDER BY clause".
0
 
kamindaCommented:
Sorry about the issue, this query would work for you

UPDATE yt SET
coumntoupdate  = bb.rownum
FROM yourtable yt
INNER JOIN
(SELECT pk,row_number() OVER (ORDER BY id) as rownum FROM yourtable) as bb ON bb.pk = yt.pk
0
 
Peter AllenAuthor Commented:
Thank you very much.
0

Featured Post

Hire Technology Freelancers with Gigs

Work with freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely, and get projects done right.

  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now