Changing the primary key

[DwayneL]

I need to run a update query to change the information in a field that will have text appened with a number increments.

Example

first record would be: text_01
secound record woulbe be: text_02
last record would be: text_03

So far I have this:

Update text Set text_id = "text_" + (select count(*)from text) + 1;

I know that the select * has to be a variable to work... I think..

Any help would be appericated.

Thanks


Dwayne

 

[DwayneL]

sorry forgot to mention that the text stays the same, its a constant.

 

[JayKusch]

Maybe this ...

declare @maxcount int
declare @newcount varchar(12)

set @maxcount = (select count(*) from text)

IF @maxcount < 10
SET newcount = &quot;text_0&quot; + Convert(char,@Maxcount)
ELSE
SET newcount = &quot;text_&quot; + Convert(char,@Maxcount)


Update text
Set text_id = @newcount
Thanks

J. Kusch

 

[JayKusch]

Sorry ... need one other item ....

after the first set of @maxcount add ..

set @maxcount + 1
Thanks

J. Kusch

 

[MeanGreen]

Kusch,
Good code but you have no way of looping. I added a few lines and here is the new code:

declare @maxcount int
declare @newcount varchar(12)

set @maxcount = (select count(*) from text)

while @maxcount > 0
begin
IF @maxcount < 10
SET @newcount = &quot;text_0&quot; + Convert(char,@Maxcount)
ELSE
SET @newcount = &quot;text_&quot; + Convert(char,@Maxcount)

Update text
Set text_id = @newcount

set @maxcount = @maxcount -1
end

Hope this helps.

 

[JayKusch]

Attaway!!! wrote that thinking in the lines of a trigger. Did not quite pick up on the fact that data was already present. Thanks again bud!
Thanks

J. Kusch

 

[DwayneL]

MeanGreen and Kusch, thanks a bunch

 

[MeanGreen]

No Problem.