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?
johnkainnAsked:
Who is Participating?
 
Ephraim WangoyaCommented:
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

Open in new window

0
 
ajcheung78Commented:
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

Open in new window


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

Open in new window

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.

All Courses

From novice to tech pro — start learning today.