• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 516
  • Last Modified:

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

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
MaxwellTurner
Asked:
MaxwellTurner
  • 3
  • 2
1 Solution
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
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
 
MaxwellTurnerAuthor Commented:
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
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
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
 
MaxwellTurnerAuthor Commented:
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
 
MaxwellTurnerAuthor Commented:
AngelIII you rock!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: Amazon Web Services - Basic

Are you thinking about creating an Amazon Web Services account for your business? Not sure where to start? In this course you’ll get an overview of the history of AWS and take a tour of their user interface.

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