update order numbers

Posted on 2011-10-20
Last Modified: 2012-05-12
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?
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

    Open in new window

    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

    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


    Featured Post

    Looking for New Ways to Advertise?

    Engage with tech pros in our community with native advertising, as a Vendor Expert, and more.

    Join & Write a Comment

    Suggested Solutions

    Title # Comments Views Activity
    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 ( 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…

    728 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    23 Experts available now in Live!

    Get 1:1 Help Now