?
Solved

SQL Server 2008 - update a column with incremented number within foreign key group

Posted on 2012-08-22
5
Medium Priority
?
513 Views
Last Modified: 2012-08-22
I've tried to search a solution for this, but I'm not sure how to descibe it in my google search and have been unsuccessful.

I have a table with 700 records.  This table has an identity field (RECORDID) as it's key, and also a foreign key.  I added a new column called ROWSORT and I need to update this column with an number starting at 1 and incrementing up by 1 for each "foreign key group".  Maybe an example will help:

RecordID     ForeignKey   RowSort (the column to update)
1                  1                   1
2                  1                   2
3                  1                   3
4                  2                   1
5                  2                   2
6                  2                   3
7                  2                   4
8                  3                   1
9                  3                   2

My TSQL just isn't strong enough to figure this out .... anyone know how to do this?

MAX
0
Comment
Question by:MaxwellTurner
  • 3
  • 2
5 Comments
 
LVL 143

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 2000 total points
ID: 38321746
you can with sql 2005+ do this.

;with data as 
( select RecordID     , ForeignKey   , RowSort  
  , ROW_NUMBER() OVER (PARTITION BY ForeignKey ORDER BY RecordID) new_row_sort
  FROM yourtable
)
UPDATE data
   SET RowSort = new_row_sort

Open in new window

0
 
LVL 1

Author Comment

by:MaxwellTurner
ID: 38321878
Thanks Angel ... I not bad at basic SQL, but a little shakey when it becomes abit advanced.  That being said, once I learn something it's there forever!

Is that a partial TSQL statement?  Do I need something else?  I copied the code and inserted the proper column names:

;with data as - not sure if this is a comment or part of the code

  ( select ReviewitemID,reviewid,RowSort  
  , ROW_NUMBER() OVER (PARTITION BY reviewid ORDER BY ReviewitemID) new_row_sort
  FROM [LV2].[dbo].[PrinterReviews_Items]
)
UPDATE [LV2].[dbo].[PrinterReviews_Items]
   SET RowSort = new_row_sort

It gives me an error "Invalid column new_row_sort"

Max
0
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 38321886
please try the code as suggested, only the table name replaced.

;with data as 
( select RecordID     , ForeignKey   , RowSort  
  , ROW_NUMBER() OVER (PARTITION BY ForeignKey ORDER BY RecordID) new_row_sort
  FROM [LV2].[dbo].[PrinterReviews_Items]
)
UPDATE data
   SET RowSort = new_row_sort
                                            

Open in new window

0
 
LVL 1

Author Comment

by:MaxwellTurner
ID: 38321912
I posted abit to quick ... for some reason I had changed "data" in the update statement with the name of my table.  Changed it back to "data" once I realized it was part of WITH ..AS above and now it works perfect!

Thanks a billion ... you are a lifesaver!

Max
0
 
LVL 1

Author Closing Comment

by:MaxwellTurner
ID: 38321920
AngelIII you rock!
0

Featured Post

NFR key for Veeam Agent for Linux

Veeam is happy to provide a free NFR license for one year.  It allows for the non‑production use and valid for five workstations and two servers. Veeam Agent for Linux is a simple backup tool for your Linux installations, both on‑premises and in the public cloud.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
We’ve all felt that sense of false security before—locking down external access to a database or component and feeling like we’ve done all we need to do to secure company data. But that feeling is fleeting. Attacks these days can happen in many w…
Is your data getting by on basic protection measures? In today’s climate of debilitating malware and ransomware—like WannaCry—that may not be enough. You need to establish more than basics, like a recovery plan that protects both data and endpoints.…

829 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question