# update order numbers

I have 2 table.
Table1 (TId, TText)
Table2 (T2Id, T1Id, OrderNumber)

In Table2 I have
Before it is like:
T2Id=1, T1Id=3, OrderNumber=1
T2Id=2, T1Id=3, OrderNumber=2
T2Id=3, T1Id=3, OrderNumber=3
T2Id=4, T1Id=3, OrderNumber=4
Now I Delete one row and then it is like this.
T2Id=1, T1Id=3, OrderNumber=1
T2Id=3, T1Id=3, OrderNumber=3
T2Id=4, T1Id=3, OrderNumber=4
I would like to update OrderNumber so it becomes like this
T2Id=1, T1Id=3, OrderNumber=1
T2Id=3, T1Id=3, OrderNumber=2
T2Id=4, T1Id=3, OrderNumber=3
How is best to do that?
###### Who is Participating?

Commented:
you can try and use a cte as follows
``````;with UpdateCTE as
(
select T2Id, T1Id, OrderNumber, row_number() over (partition by T1Id order by T2Id, OrderNumber) rn
from Table2
)

update UpdateCTE
set OrderNumber = rn
``````
0

Commented:
You can create an update statement to update the ordernumber using the following

``````select x.*, row_number() over (order by t2ld, t1ld) as update_ordernumber from table2 as x
``````

Here is an example of the results:

``````select x.*, row_number() over (order by t2ld, t1ld) as update_ordernumber from
(
select 1 t2ld, 3 t1ld, 1 ordernumber union
select 2 t2ld, 3 t1ld, 2 ordernumber union
--select 3 t2ld, 3 t1ld, 3 ordernumber union
select 4 t2ld, 3 t1ld, 4 ordernumber
) x
``````
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.