Solved

# update order numbers

Posted on 2011-10-20
187 Views
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?
0
Question by:johnkainn

LVL 32

Accepted Solution

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

LVL 3

Expert Comment

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

## Featured Post

### Suggested Solutions

index rebuild failed 8 76
SQL Management Studio Install 11 59
Need help creating a stored procedure 4 47
SQL Select - AVG 3 36
After restoring a Microsoft SQL Server database (.bak) from backup or attaching .mdf file, you may run into "Error '15023' User or role already exists in the current database" when you use the "User Mapping" SQL Management Studio functionality to al…
Hi all, It is important and often overlooked to understand “Database properties”. Often we see questions about "log files" or "where is the database" and one of the easiest ways to get general information about your database is to use “Database p…
Illustrator's Shape Builder tool will let you combine shapes visually and interactively. This video shows the Mac version, but the tool works the same way in Windows. To follow along with this video, you can draw your own shapes or download the file…
Here's a very brief overview of the methods PRTG Network Monitor (https://www.paessler.com/prtg) offers for monitoring bandwidth, to help you decide which methods you´d like to investigate in more detail.  The methods are covered in more detail in o…