Help with query needed!

Hi,

I have a table of this form:

| ID | Code | Calc

1 A 0
2 A 0
3 X 0
4 U 0
5 P 0
6 A 0
7 P 0
8 P 0
9 F 0
10 J 0
11 A 0
12 P 0
13 A 0

How can I write an update statement so that the Calc column is updated in such a way that
- if the Code column's value is equal to 'P', the Calc's value will be the value of the previous record's Calc value plus 1
- if the Code column's value is not equal to 'P', the Calc's value will be the same as the value of the previous record's Calc value

| ID | Code | Calc

1 A 0
2 A 0
3 X 0
4 U 0
5 P 1
6 A 1
7 P 2
8 P 3
9 F 3
10 J 3
11 A 3
12 P 4
13 A 4

Thanks

here you go..

Code Snippet

Create Table #calctable (

[ID] int ,

[Code] Varchar(100) ,

[Calc] int

);

Insert Into #calctable Values('1','A','0');

Insert Into #calctable Values('2','A','0');

Insert Into #calctable Values('3','X','0');

Insert Into #calctable Values('4','U','0');

Insert Into #calctable Values('5','P','0');

Insert Into #calctable Values('6','A','0');

Insert Into #calctable Values('7','P','0');

Insert Into #calctable Values('8','P','0');

Insert Into #calctable Values('9','F','0');

Insert Into #calctable Values('10','J','0');

Insert Into #calctable Values('11','A','0');

Insert Into #calctable Values('12','P','0');

Insert Into #calctable Values('13','A','0');

Update #calctable

Set

Calc = Data.NewCalc

From

#calctable Tbl

Join

(

SelectId

,(Select Count(*) From #calctable Sub Where Sub.Code='P' And Sub.Id<=Main.Id) as NewCalc

From

#calctable Main

)as Data On Tbl.Id = Data.Id

Select * From #calctable

|||Thanks!

Considering there will be millions of records within this table, how efficient is this method? Could there be a better way to do this?
|||Sorry, I was not intending to edit your response; I guess I clicked the wrong button. Does your table have a clustered index that is based on the ID column?|||

Code Snippet

select t1.*,min([t3].[PRank])

from <MyTable> as [t1]

inner join

(select [t2].[ID] ,row_number() over (order by [t2].[ID]) - 1 as [PRank] from <MyTable> as [t2] where t2.Code = 'P') as [t3]

on t1.ID < t2.Id

group by t1.ID,t1.code,t1.calc

order by t1.id

|||The table is a temporary one, so I can create any index needed.
|||

rusag2 wrote:

Code Snippet

select t1.*,min([t3].[PRank])

from <MyTable> as [t1]

inner join

(select [t2].[ID] ,row_number() over (order by [t2].[ID]) - 1 as [PRank] from <MyTable> as [t2] where t2.Code = 'P') as [t3]

on t1.ID < t2.Id

group by t1.ID,t1.code,t1.calc

order by t1.id

the join clause should be

Code Snippet

t1.ID < t3.Id