Update a column based on row_number()

Question

i created a new not-null column with default value 0 for my table and it keeps display orders. I want to update all rows for that table, that displayorder has the value of row_number() over id ordered. here i can do this for one id. How can i do that for all ids.

my table is:

id | personid | name   | displayorder
---+----------+--------+------------
 1 |    10    | test1  |    0
 2 |    10    | test2  |    0
 3 |    10    | test3  |    0
 4 |    10    | test4  |    0
 5 |    10    | test5  |    0
 6 |    11    | test6  |    0
 7 |    11    | test7  |    0
 8 |    12    | test8  |    0

i want the result is:

id | personid | name   | displayorder
---+----------+--------+------------
 1 |    10    | test1  |    1
 2 |    10    | test2  |    2
 3 |    10    | test3  |    3
 4 |    10    | test4  |    4
 5 |    10    | test5  |    5
 6 |    11    | test6  |    1
 7 |    11    | test7  |    2
 8 |    12    | test8  |    1

here is my sql code, but it only works for just one given id:

update MyTable
set displayorder = z.ord
 FROM  (
   SELECT row_number() over (order by id) as ord, id 
     FROM MyTable p2
   where p2.personid = 1
   ) z
  where MyTable.id= z.id
  and personid = 1

do you really have 2 tables - `productproperty` and `MyTable` or is it the same table? — Roman Pekar, May 28 '15 at 11:27
then you definitely better go with answer without join, I think it will be simplest and fastest one — Roman Pekar, May 28 '15 at 11:55
instead of insisting to use SQL, it will be better switching to T-SQL and utilizing loop capabilities. ...or, not to use displayorder column at all and create the order on the fly. i cannot imagine the effort you spend to maintain data integrity on the column. just my 2 cents. — tjeloep, May 28 '15 at 12:04

score 10 · Accepted Answer · answered May 28 '15 at 11:50

Use This Code:

 Create  TABLE #order
    (
        Id INT,
        PersonId INT,
        Name NVARCHAR(25),
        DisplayOrder INT
    )

    INSERT INTO #ORDER VALUES(1 , 10 , 'test1',0 )
    INSERT INTO #ORDER VALUES(2 , 10 , 'test2',0 )
    INSERT INTO #ORDER VALUES(3 , 10 , 'test3',0 )
    INSERT INTO #ORDER VALUES(4 , 10 , 'test4',0 )
    INSERT INTO #ORDER VALUES(5 , 10 , 'test5',0 )
    INSERT INTO #ORDER VALUES(6 , 11 , 'test6',0 )
    INSERT INTO #ORDER VALUES(7 , 11 , 'test7',0 )
    INSERT INTO #ORDER VALUES(8 , 12 , 'test8',0 )



 update #order 
    Set #order.DisplayOrder=R.DisplayOrder
    from(select id,ROW_NUMBER() over (partition by S.personid  order by S.id) as DisplayOrder
    from #order S) R
    where #order.Id=R.id

    select * from #order

thank you it worked. I just learned about "partition by" thanks. — Can, May 28 '15 at 12:01
thanks Can for explaining my problem so well, and thanks Hell Boy for the pretty and simple soultion! — Gooloosh, Jan 02 '20 at 08:27

score 5 · Answer 2 · answered May 28 '15 at 11:44

if productproperty and MyTable are really the same table, I'd do this:

with cte as (
    select
        t.displayorder,
        row_number() over(partition by t.PersonId order by t.Id) as rn
    from MyTable as t
)
update cte set displayorder = rn

This one using the fact that common table expressions are treated like updatable views in SQL Server.

Or even create the view with row_number() as displayorder, so it will be recalculated automatically, so you don't have to update your table every time you add/remove row.

The beauty of @Roman Pekar answer is no need to a primary key and still use one table. — Marwan Almukh, Jun 22 '19 at 06:35

score 1 · Answer 3 · answered May 28 '15 at 11:37

1

A quick solution is using the ranking facility. The example should do what you asked for.

DECLARE @Pid TABLE
(
    Id INT,
    PersonId INT,
    Name NVARCHAR(25),
    DisplayOrder INT
)

INSERT INTO @Pid
VALUES
(1 , 10 , 'test1',0 ),
(2 , 10 , 'test2',0 ),
(3 , 10 , 'test3',0 ),
(4 , 10 , 'test4',0 ),
(5 , 10 , 'test5',0 ),
(6 , 11 , 'test6',0 ),
(7 , 11 , 'test7',0 ),
(8 , 12 , 'test8',0 )

UPDATE pid
SET DisplayOrder = dpid.rank
FROM @Pid pid
INNER JOIN 
(
    SELECT *, Rank() OVER(Partition by PersonId Order by Id) as rank
    FROM @Pid
 )dpid ON dpid.Id = pid.Id

 SELECT * 
 FROM @Pid

answered May 28 '15 at 11:37

TYY

2,702
1
13
14

it the original question there're 2 tables, and you have only one. I'm 90% sure that there're should be only one table, but I'd wait for topic starter comment – Roman Pekar May 28 '15 at 11:41
I figured that if the OP was joining by ID it would not matter if it is one or 2 tables (I would hope not, but you never know). The update statement in the example above would just have to be changed to update the second table. – TYY May 28 '15 at 11:46

Update a column based on row_number()

3 Answers3

Related