Link to home
Start Free TrialLog in
Avatar of johnkainn
johnkainn

asked on

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?
ASKER CERTIFIED SOLUTION
Avatar of Ephraim Wangoya
Ephraim Wangoya
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
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