reorder numbers in asc order then update

#1

Hello all

I'm having a problem reordering my id_order column

sample table
ID ID_ORDER
1 1
1 3
1 4
2 1
2 2
3 1
3 2
3 3
3 5

I would like to know the best approach to update the id_order column.
ID ID_ORDER
1 1
1 2
1 3
2 1
2 2
3 1
3 2
3 3
3 4

Thanks in advance

#2

On 2012-01-03 17:15, Business Minded Men wrote:

Quote:

I'm having a problem reordering my id_order column

sample table
ID ID_ORDER
1 1
1 3
1 4
2 1
2 2
3 1
3 2
3 3
3 5

I would like to know the best approach to update the id_order column.
ID ID_ORDER
1 1
1 2
1 3
2 1
2 2
3 1
3 2
3 3
3 4

Thanks in advance

WITH corrected_table AS (
SELECT ID_ORDER, ROW_NUMBER() OVER (PARTITION BY ID ORDER BY ID_ORDER)
AS NEW_ID_ORDER
FROM sample_table
)
UPDATE corrected_table SET ID_ORDER = NEW_ID_ORDER;

--
J.

#3

On Jan 3, 1:16*pm, Jeroen Mostert <jmost... (AT) xs4all (DOT) nl> wrote:

Quote:

On 2012-01-03 17:15, Business Minded Men wrote:

I'm having a problem reordering my id_order column

sample table
ID * * * * * *ID_ORDER
1 *1
1 *3
1 *4
2 *1
2 *2
3 *1
3 *2
3 *3
3 *5

I would like to know the best approach to update the id_order column.
ID * * * * * *ID_ORDER
1 *1
1 *2
1 *3
2 *1
2 *2
3 *1
3 *2
3 *3
3 *4

Thanks in advance

* *WITH corrected_table AS (
* * *SELECT ID_ORDER, ROW_NUMBER() OVER (PARTITION BY ID ORDER BY ID_ORDER)
AS NEW_ID_ORDER
* * *FROM sample_table
* *)
* *UPDATE corrected_table SET ID_ORDER = NEW_ID_ORDER;

--
J.- Hide quoted text -

- Show quoted text -

Thanks J

Lets say I wanted to start ID_ORDER at a specific number and increment
from there so all ID_ORDERS are unique. Can I declare a variable and
increment after each update?

#4

Business Minded Men (businessmindedmen (AT) gmail (DOT) com) writes:

Quote:

Lets say I wanted to start ID_ORDER at a specific number and increment
from there so all ID_ORDERS are unique. Can I declare a variable and
increment after each update?

You cannot use variables in an all-at-once statement.

But if you just take out the PARTITION BY clause you get what you ask
for.

--
Erland Sommarskog, SQL Server MVP, esquel (AT) sommarskog (DOT) se

Links for SQL Server Books Online:
SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

reorder numbers in asc order then update

microsoft.public.sqlserver.server microsoft.public.sqlserver.server

reorder numbers in asc order then update - 01-03-2012 , 10:15 AM

Re: reorder numbers in asc order then update - 01-03-2012 , 12:16 PM

Re: reorder numbers in asc order then update - 01-03-2012 , 01:54 PM

Re: reorder numbers in asc order then update - 01-03-2012 , 02:44 PM