CODEWITHSUNDEEP
sqlsql-servert-sql
Denis Biondic
Denis Biondic

Reputation: 8201

Is there a way to update groups of rows with separate incrementing values in one query

Lets say you have the following table:

Id Index
1  3
1  1
2  1
3  3
1  5

what I would like to have is the following:

Id Index
1  0
1  1
2  0
3  0
1  2

As you might notice, the goal is for every row where Id is the same, to incrementally update the Index column, starting from zero.

Now, I know this is fairly simple with using cursors, but out of curiosity is there a way to do this with single UPDATE query, somehow combining with temp tables, common table expressions or something similar?

Upvotes: 1

Views: 93

Answers (5)

Deepshikha
Deepshikha

Reputation: 10264

With Row_number() -1 and CTE you can write as:

CREATE TABLE #temp1(
Id int,
[Index] int)
INSERT INTO #temp1 VALUES (1,3),(1,1),(2,1),(3,3),(1,5);

--select * from #temp1;
With CTE as
(
    select t.*, row_number() over (partition by id order by (select null))-1 as newindex
    from #temp1 t
)
Update CTE 
set [Index] = newindex;

select * from #temp1;

Demo

Upvotes: 2

Suvendu Shekhar Giri
Suvendu Shekhar Giri

Reputation: 1384

Probably, this is what you want

SELECT *,RANK() OVER(PARTITION BY Id ORDER BY [Index])-1 AS NewIndex FROM
(
    SELECT 1 AS Id,3 [Index]
    UNION
    SELECT 1,1
    UNION
    SELECT 2,1
    UNION
    SELECT 3,3
    UNION
    SELECT 1,5
) AS T

& the result will come as
enter image description here

Now if you want to update the table then execute this script

UPDATE tblname SET Index=RANK() OVER(PARTITION BY t.Id ORDER BY t.[Index])-1 
FROM tblname AS t

In case I am missing something or any further assistance is required please let me know.

Upvotes: 0

Ravendarksky
Ravendarksky

Reputation: 633

I'm not sure why you would want to do this really, but I had fun figuring it out! This solution relies on your table having a primary key for the self join... but you could always create an auto inc index if none exists and this is a one off job... This will also have the added benefit of getting you to think about the precise ordering of this you want... as currently there is no way of saying which order [ID] will get [Index] in.

UPDATE dbo.Example
SET [Index] = b.newIndex
FROM dbo.Example a
INNER JOIN (
    select 
        z.ID,
        z.[Index],
        (row_number() over (partition by ID order by (select NULL))) as newIndex
    from Example z
) b ON a.ID = b.ID AND a.[Index]=b.[Index] --Is this a unique self join for your table?.. no PK provided. You might need to make an index first.

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269513

Yes, assuming that the you don't really care about the order of the values for the new index values. SQL Server offers updatable CTEs and window functions that do exactly what you want:

with toupdate as (
      select t.*, row_number() over (partition by id order by (select NULL)) as newindex
      from table t
     )
update toupdate
    set index = newindex;

If you want them in a specific order, then you need another column to specify the ordering. The existing index column doesn't work.

Upvotes: 2

glaeran
glaeran

Reputation: 426

CREATE TABLE #temp1(
Id int,
Value int)

INSERT INTO #temp1 VALUES (1,2),(1,3),(2,3),(4,5)


SELECT
Id
,Value 
,ROW_NUMBER() OVER (PARTITION BY Id ORDER BY Id) Id
FROM #temp1

Start with this :)

Gave me results like

Id Value Count
1 2 1
1 3 2
1 2 3
1 3 4
1 2 5
1 3 6
1 2 7
1 3 8
2 3 1
2 4 2
2 5 3
2 3 4
2 4 5
2 5 6
2 4 7
2 5 8
2 3 9
2 3 10
3 4 1
4 5 1
4 5 2
4 5 3
4 5 4

Upvotes: -1

Related Questions