asked on

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

ASKER CERTIFIED SOLUTION

Guy Hengel [angelIII / a3]

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

MaxwellTurner

ASKER

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

Guy Hengel [angelIII / a3]

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

MaxwellTurner

ASKER

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

MaxwellTurner

ASKER

AngelIII you rock!