CODEWITHSUNDEEP
sql-server
user3548593
user3548593

Reputation: 499

Modifying a column in a table

I have the following table test 

iD Name    ParentId   GroupID
-----------------------------
1            1        Null
2            1        Null
3            1        Null
4            7        Null
5            7        Null
6            7        Null
7            9        Null

How can I modify it to get the column GroupID like this:

 iD Name    ParentId   GroupID
 ------------------------------
    1            1        1
    2            1        1
    3            1        1
    4            7        2
    5            7        2
    6            7        2
    7            9        3

Upvotes: 1

Views: 50

Answers (2)

M.Ali
M.Ali

Reputation: 69524

; WITH CTE 
AS
 (
 SELECT iDName, ParentId, GroupID,
        DENSE_RANK() OVER (ORDER BY ParentId ASC) RN
  FROM Test
 )
UPDATE CTE 
SET GroupID = RN

Working SQL FIDDLE

Upvotes: 2

Jainendra
Jainendra

Reputation: 25143

UPDATE test SET GroupID = 1 WHERE ParentId = 1
UPDATE test SET GroupID = 2 WHERE ParentId = 7
UPDATE test SET GroupID = 3 WHERE ParentId = 9

Upvotes: 1

Related Questions