Update column based on row_number ()
i created a new column not null, default is 0 for my table and it preserves the display orders. I want to update all rows for this table that displayorder has a row_number () value above the ordered id. here i can do it for one id. How can I do this for all ids.
my table:
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:
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 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
source to share
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
source to share
if productproperty
u MyTable
are indeed the same table, I would do the following:
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 fact is exploited in that generic table expressions are treated as updatable views in SQL Server.
Or even create a view using row_number()
as display, so it will be recalculated automatically so you don't have to update the table every time you add / remove a row.
source to share
The quick solution is using ranking. 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
source to share