Resequencing Question

[gcoast]

My current table (3 million records) has this structure.

PN Grp Seq
----------------------
111 0 10
111 0 20
111 0 30
111 1 10
111 1 20
111 1 30
111 1 40
222 0 10
222 0 20
222 0 30
222 1 10
222 1 20
222 1 30
.
.
.

I need to produce this output without using a cursor. Resequence the Seq field each time the PN field changes, regardless of which group.

PN Grp Seq
----------------------
111 0 1
111 0 2
111 0 3
111 1 4
111 1 5
111 1 6
111 1 7
222 0 1
222 0 2
222 0 3
222 1 4
222 1 5
222 1 6
.
.
.


Any ideas on how to achieve this result?

Thanks in advance.

 

[TheBugSlayer]

Well, it looks like you already have the sequence there but it multiples of ten. If that is the case a simple update should work:

UPDATE tt_table SET = seq/10

 

[gcoast]

No, that would give me the following result. I need to resequence for each PN not each PN + Grp.

PN Grp Seq
----------------------
111 0 1
111 0 2
111 0 3
111 1 1
111 1 2
111 1 3
111 1 4
222 0 1
222 0 2
222 0 3
222 1 1
222 1 2
222 1 3
.
.
.

 

[TheBugSlayer]

The fact that you don' t want to use cursors makes this a little complicater. Here is an idea:

1-Create a table with the same structure as your table plus an identity field.
2-Insert a group of PNs from the original table into this one; the identitity field will automatically take incremental values.
3-Update the original table overwriting SEQ field with the identity field.
4-Drop the new table
5- Repeat the process from step 1 until all distinct PNs have been processed.



CREATE TABLE [dbo].[tt_table2] (
[PN] [char] (7) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[GRP] [char] (5) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[SEQ] [int] NOT NULL ,
[NewSeq] [int] IDENTITY (1, 1) NOT NULL
) ON [PRIMARY]
GO
select distinct pn from tt_table
insert into tt_table2 select * from tt_table where pn = 'one PN by one'
update tt_table t1 set t1.seq = t2.newseq join tt_table2 t2 on --match all three fields in tt_table
drop table tt_table2


I am sure you will find a way to automate this.