CODEWITHSUNDEEP
sql-server-2008
user3011414
user3011414

Reputation: 119

Update Order column with number increasing (depend on ID column)

I'm using the SQL Server 2008.

Now I have the scenario as description below:

Now I want to update Order column with number increasing (start from 1) for each ID,Name:

    No  Name    Order
1   AAA 1
1   AAA 2
1   AAA 3
1   AAA 4
1   AAA 5
2   BBB 1
2   BBB 2
2   BBB 3

How can I get this result without using Cursors?

I'm very appriciated for your help.

Thanks.

Upvotes: 1

Views: 105

Answers (1)

Leons
Leons

Reputation: 2674

You can try the following. I use CTE to drive the update statement

WITH data AS
(
    SELECT Order
           , ROW_NUMBER() OVER (PARTITION BY ID, NAME 
                                ORDER BY ID, NAME) AS Seq
      FROM TableA
)
UPDATE data
   SET Order = Seq

Substitute TableA with your table name

Upvotes: 2

Related Questions