CODEWITHSUNDEEP
sql-serversql-server-2008
kinkajou
kinkajou

Reputation: 3728

Updating Sql Server Row with increasing ID

How do I update sql server with increasing ID according to another column?

I have a table with following structure:

sn  name    val
    test     0
    test     0.5
    test     2
    test1    0
    test1    0.5
    test1     2

How do i update it so that is will be:

sn  name      val
1    test      0
2    test      0.5
3    test      2
1    test1     0
2    test1     0.5
3    test1     2

fiddle

Upvotes: 2

Views: 216

Answers (3)

valex
valex

Reputation: 24144

You should use ROW_NUMBER() function. Also as soon as you have to start new counter for each NAME value you should use PARTITON by NAME in this statement.

WITH T AS 
(select sql_test.*,
        ROW_NUMBER() OVER (PARTITION BY Name ORDER BY Val) as RowNumber
        from sql_test)
UPDATE T SET SN=RowNumber;

SQLFiddle demo

Upvotes: 1

Cristian Lupascu
Cristian Lupascu

Reputation: 40536

This can easily be done using Row_Number() and the OVER ... PARTITION BY clause, if you have a key column in the table. I added a column: Id int identity primary key and here's the update:

;with RowNumberedData as (
  select 
    id, 
    row_number() over (
      partition by name
      order by id
    ) as rowno
  from sql_test
)
update s
  set sn = r.rowno
from sql_test s
join RowNumberedData r
  on s.id = r.id;

SQLFiddle: http://sqlfiddle.com/#!3/43fa8/4

Upvotes: 1

Mathese F
Mathese F

Reputation: 559

Use cte and row_number

Here is an example : 

create table #mytable (sn int, name varchar(20), val money)

insert into #mytable values (null, 'test', 0.5)
insert into #mytable values (null, 'test', 1)
insert into #mytable values (null, 'test1', 0.5)
insert into #mytable values (null, 'test1', 1)

;with cte as (select row_number() over (order by name, val) as rn, * from #mytable)
update cte set sn = rn

select * from #mytable

Upvotes: 2

Related Questions