CODEWITHSUNDEEP
sqlsql-server
Amr Kamal
Amr Kamal

Reputation: 137

How to assign ROW_NUMBER() to column

I have two tables UCP , TerritoriesInfo

UCP have TerritoriesID , Territories

TerritoriesInfo have ID , TerritoriesName

I want to update TerritoriesID with Row_Number()

for example

UCP

TerritoriesID            Territories
1                          NULL
2                          NULL
3                          NULL
4                          NULL
5                          NULL
6                          NULL
7                          NULL

TerritoriesInfo

ID                 TerritoriesName
1                          A
2                          B
3                          C
4                          D
5                          ES
6                          T
7                          R

First i want to assign UCP.TerritoriesID = ROW_NUMBERS() second i want to Update UCP.Territories from TerritoriesInfo.TerritoriesName where UCP.TerritoriesID = TerritoriesInfo.ID I tried Auto increment but it doesn't work when i use delete statement and try again then i tried this query but doesn't work 

update UCP SET TerritoriesID = ROW_NUMBER()

Upvotes: 0

Views: 169

Answers (1)

Ed Harper
Ed Harper

Reputation: 21505

Because UCP.TerritoriesID and TerritoriesInfo.ID contain one-based sequences without gaps in the sample data, it's unclear what the requirement is here. My best guess is that the real UCP.TerritoriesID data is either non-sequential or doesn't start at one.

ROW_NUMBER requires an ORDER BY in its OVER clause. I'm guessing that the row numbers should be assigned based on the current value of UCP.TerritoriesID, pending more information:

;WITH numCTE
AS
(   SELECT TerritoriesID, ROW_NUMBER() OVER (ORDER BY TerritoriesID) AS rn
    FROM UCP
)
UPDATE numCTE
SET TerritoriesID = rn

(I'm assuming that once you have this, you can handle the update of UCP.Territories)

Upvotes: 2

Related Questions