Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium


update order numbers

Posted on 2011-10-20
Medium Priority
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

Ephraim Wangoya earned 500 total points
ID: 37003650
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


Expert Comment

ID: 37003657
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

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I have written a PowerShell script to "walk" the security structure of each SQL instance to find:         Each Login (Windows or SQL)             * Its Server Roles             * Every database to which the login is mapped             * The associated "Database User" for this …
Long way back, we had to take help from third party tools in order to encrypt and decrypt data.  Gradually Microsoft understood the need for this feature and started to implement it by building functionality into SQL Server. Finally, with SQL 2008, …
Despite its rising prevalence in the business world, "the cloud" is still misunderstood. Some companies still believe common misconceptions about lack of security in cloud solutions and many misuses of cloud storage options still occur every day. …
When cloud platforms entered the scene, users and companies jumped on board to take advantage of the many benefits, like the ability to work and connect with company information from various locations. What many didn't foresee was the increased risk…

571 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