asked on

update order line number with sequential number

I need to update a table which has multiple sales orders which could have multiple lines.
I need to renumber the order lines for each distinct order number.

SalesOrder SalesOrderLine
0001234 1
0001234 586
0001235 1
etc....
0001235 6

Needs to be

SalesOrder SalesOrderLine
0001234 1
0001234 2
0001235 1
0001235 2
etc...

There are around 500 SalesOrders and they could have up to 10 SalesOrderLine numbers each.

Thanks in advance

ASKER CERTIFIED SOLUTION

Marcus Keustermans

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Richard Cooper

ASKER

Hi that s got it. But I need to update the Line Numbers to the ones shown on the select statement if possible.

Richard Cooper

ASKER

Hi Marcus,

Done thanks.

Richard Cooper

ASKER

Thank you for your help.

i never thought of using the ROW_NUMBER.

Marcus Keustermans

Your welcome.

Marcus Keustermans

This will do it.

SELECT TOP (1000) [salesorder]
      ,[salesorderline]
  FROM [asdfrewq].[dbo].[SalesOrder]

UPDATE SO
SET salesorderline = SO2.newsalesorderline  
FROM [asdfrewq].[dbo].[SalesOrder] SO
JOIN ( SELECT salesorder,salesorderLine, ROW_NUMBER() OVER (PARTITION BY salesorder order by salesorderline) newsalesorderline
FROM [asdfrewq].[dbo].[SalesOrder]
) SO2 ON SO2.salesorder = SO.salesorder and SO2.salesorderline = SO.salesorderline

SELECT TOP (1000) [salesorder]
      ,[salesorderline]
  FROM [asdfrewq].[dbo].[SalesOrder]

Open in new window

If you add an order by salesorder, salesorder in the select statement after the update then it will look like the result of my first post

SOLUTION

Marcus Keustermans

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Richard Cooper

ASKER

Thank you so much Marcus.

Marcus Keustermans

You're most welcome.