Repost: Solution didn't work any other ideas??

hi Ron,
It didn't work, as soon as I add an identity column,
it inserts running nos. into the field.
Am I missing something?
Thank you,
-Kamy
>--Original Message--
>Kamy,
>
>This will do it at least as you specified.
>
>SET NOCOUNT ON
>GO
>IF OBJECT_ID('dbo.test') IS NOT NULL DROP TABLE dbo.test
>GO
>CREATE TABLE dbo.test (TestCol varchar(10), Seq int)
>GO
>INSERT test VALUES ('A001',0)
>INSERT test VALUES ('A001',0)
>INSERT test VALUES ('A002',0)
>INSERT test VALUES ('A002',0)
>INSERT test VALUES ('A002',0)
>INSERT test VALUES ('A003',0)
>INSERT test VALUES ('A003',0)
>INSERT test VALUES ('B011',0)
>INSERT test VALUES ('B011',0)
>INSERT test VALUES ('B011',0)
>INSERT test VALUES ('C189',0)
>INSERT test VALUES ('D9090',0)
>GO
>ALTER TABLE dbo.test add Ctr int not null identity(1,1)
>GO
>SELECT t1.TestCol
> , (SELECT COUNT(*) FROM test t2 WHERE t1.testcol = t2.testcol AND t1.Ctr
>>= t2.Ctr)
>FROM test t1
>GO
>ALTER TABLE dto.test DROP COLUMN Ctr
>
>Hope this helps,
>Ron
>--
>Ron Talmage
>SQL Server MVP
>
>"Kamy" <anonymous@.discussions.microsoft.com> wrote in
message
>news:0ccb01c49c09$2a105a50$a301280a@.phx.gbl...
>> I have a need to update data in a table so that
>> a column contains the generated number for sets of
records
>> for instance I have fileName as listed and it should
>> generate the SheetNum for each of the set
>>
>> FileName SheetNum
>> A001 1
>> A001 2
>> A002 1
>> A002 2
>> A002 3
>> A003 1
>> A003 2
>> B011 1
>> B011 2
>> B011 3
>> C189 1
>> D9090 1
>>
>> Thank you in advance
>> -Kamy
>> .
>>
>>You need to update using a query and the PK. That identity would be a useful
PK for this
alter table tbl add seq int
update tbl set seq = (select count(*) from tbl t1 where t1.sheet = t.sheet
and t1.pk <= t.pk)
from tbl t
"Kamy" wrote:
> hi Ron,
> It didn't work, as soon as I add an identity column,
> it inserts running nos. into the field.
> Am I missing something?
> Thank you,
> -Kamy
> >--Original Message--
> >Kamy,
> >
> >This will do it at least as you specified.
> >
> >SET NOCOUNT ON
> >GO
> >IF OBJECT_ID('dbo.test') IS NOT NULL DROP TABLE dbo.test
> >GO
> >CREATE TABLE dbo.test (TestCol varchar(10), Seq int)
> >GO
> >INSERT test VALUES ('A001',0)
> >INSERT test VALUES ('A001',0)
> >INSERT test VALUES ('A002',0)
> >INSERT test VALUES ('A002',0)
> >INSERT test VALUES ('A002',0)
> >INSERT test VALUES ('A003',0)
> >INSERT test VALUES ('A003',0)
> >INSERT test VALUES ('B011',0)
> >INSERT test VALUES ('B011',0)
> >INSERT test VALUES ('B011',0)
> >INSERT test VALUES ('C189',0)
> >INSERT test VALUES ('D9090',0)
> >GO
> >ALTER TABLE dbo.test add Ctr int not null identity(1,1)
> >GO
> >SELECT t1.TestCol
> > , (SELECT COUNT(*) FROM test t2 WHERE t1.testcol => t2.testcol AND t1.Ctr
> >>= t2.Ctr)
> >FROM test t1
> >GO
> >ALTER TABLE dto.test DROP COLUMN Ctr
> >
> >Hope this helps,
> >Ron
> >--
> >Ron Talmage
> >SQL Server MVP
> >
> >"Kamy" <anonymous@.discussions.microsoft.com> wrote in
> message
> >news:0ccb01c49c09$2a105a50$a301280a@.phx.gbl...
> >> I have a need to update data in a table so that
> >> a column contains the generated number for sets of
> records
> >> for instance I have fileName as listed and it should
> >> generate the SheetNum for each of the set
> >>
> >> FileName SheetNum
> >> A001 1
> >> A001 2
> >> A002 1
> >> A002 2
> >> A002 3
> >> A003 1
> >> A003 2
> >> B011 1
> >> B011 2
> >> B011 3
> >> C189 1
> >> D9090 1
> >>
> >> Thank you in advance
> >> -Kamy
> >> .
> >>
> >>
>|||Nigel,
Thanks for taking time to reply! But this also does not
solve my problem, it increments all numbers in order from
1 to ...
I don't want it to do that, please take a look at my
results again, for a given set I would like it to
increment nos, and then start with 1 again.
Thank you again,
-Kamy
>--Original Message--
>You need to update using a query and the PK. That
identity would be a useful
>PK for this
>alter table tbl add seq int
>update tbl set seq = (select count(*) from tbl t1 where
t1.sheet = t.sheet
>and t1.pk <= t.pk)
>from tbl t
>"Kamy" wrote:
>> hi Ron,
>> It didn't work, as soon as I add an identity column,
>> it inserts running nos. into the field.
>> Am I missing something?
>> Thank you,
>> -Kamy
>> >--Original Message--
>> >Kamy,
>> >
>> >This will do it at least as you specified.
>> >
>> >SET NOCOUNT ON
>> >GO
>> >IF OBJECT_ID('dbo.test') IS NOT NULL DROP TABLE
dbo.test
>> >GO
>> >CREATE TABLE dbo.test (TestCol varchar(10), Seq int)
>> >GO
>> >INSERT test VALUES ('A001',0)
>> >INSERT test VALUES ('A001',0)
>> >INSERT test VALUES ('A002',0)
>> >INSERT test VALUES ('A002',0)
>> >INSERT test VALUES ('A002',0)
>> >INSERT test VALUES ('A003',0)
>> >INSERT test VALUES ('A003',0)
>> >INSERT test VALUES ('B011',0)
>> >INSERT test VALUES ('B011',0)
>> >INSERT test VALUES ('B011',0)
>> >INSERT test VALUES ('C189',0)
>> >INSERT test VALUES ('D9090',0)
>> >GO
>> >ALTER TABLE dbo.test add Ctr int not null identity
(1,1)
>> >GO
>> >SELECT t1.TestCol
>> > , (SELECT COUNT(*) FROM test t2 WHERE t1.testcol =>> t2.testcol AND t1.Ctr
>> >>= t2.Ctr)
>> >FROM test t1
>> >GO
>> >ALTER TABLE dto.test DROP COLUMN Ctr
>> >
>> >Hope this helps,
>> >Ron
>> >--
>> >Ron Talmage
>> >SQL Server MVP
>> >
>> >"Kamy" <anonymous@.discussions.microsoft.com> wrote in
>> message
>> >news:0ccb01c49c09$2a105a50$a301280a@.phx.gbl...
>> >> I have a need to update data in a table so that
>> >> a column contains the generated number for sets of
>> records
>> >> for instance I have fileName as listed and it should
>> >> generate the SheetNum for each of the set
>> >>
>> >> FileName SheetNum
>> >> A001 1
>> >> A001 2
>> >> A002 1
>> >> A002 2
>> >> A002 3
>> >> A003 1
>> >> A003 2
>> >> B011 1
>> >> B011 2
>> >> B011 3
>> >> C189 1
>> >> D9090 1
>> >>
>> >> Thank you in advance
>> >> -Kamy
>> >> .
>> >>
>> >>
>>
>.
>