Updating primary key using cursor

[Jamfool]

Ok basically iam using a cursor to scroll thru the record set, and change the primary key to prefix a 'X char on the front. I do this by stripping off the first 3 chars, and then taking the last 11 and thus provide space for the 'X'.

I need to update this key i.e new_code=old_code where something? = something? problem is all the other fields can be null. so what do i do?

do i need an index temp table or something?

*However I dont think this is the best way so I would like some guidance as to the best approach.



declare @Code varchar(15)
declare @Int_EntityCode varchar(15)
declare @EntityCode varchar(15)
declare @added_prefix_entity_code varchar(15)
declare @prefix_entity_code varchar(15)
declare @suffix_entity_code varchar(15)
select @Int_EntityCode = '000000000000000'
select @prefix_entity_code = ' '
select @suffix_entity_code = '0'
select @added_prefix_entity_code = 'X'
declare @zerofill int
select @zerofill = '0'


DECLARE CUR_Multi_Insert SCROLL CURSOR FOR
SELECT Code
FROM entities

OPEN CUR_Multi_Insert
FETCH NEXT FROM CUR_Multi_Insert
INTO @Code

while @@fetch_status = 0

BEGIN

--Entity Code Construction

select @entitycode = @code
print @entitycode

select @prefix_entity_code = left (@Code,3)
print @prefix_entity_code

select @suffix_entity_code = right (@Code,11)
print @suffix_entity_code

select @EntityCode = @added_prefix_entity_code+@prefix_entity_code+@suffix_entity_code
print @EntityCode


FETCH NEXT FROM CUR_Multi_Insert
INTO @code
END

CLOSE CUR_Multi_Insert
DEALLOCATE CUR_Multi_Insert

 

[swampBoogie]

What's wrong with

update entities 
set code = 'x' + substring(code,1,3) + right (code,11)

?

 

[Jamfool]

cheers for that, just me being slow this morning

 

[Jamfool]

ok thanks again, It would be great if you could help me with my main problem, basically I have two companies merging records with an alpha-numeric client code

e.g abc000000000001

The intitial prefix of an 'x' char as allowed me to input the two record sets, however i need to reindex the smaller company client codes e.g

if the larger company has client codes from abc000000000001 to say abc000000001001 then i need to label any conflicting codes from the smaller company to the next numerical value. so that abc000000000049 in the smaller company would become abc000000001002 in the main database (Obviously i need to remove the 'x' prefix and put a zero back in between the alpha-numeric string)

Note I cannot simply keep the prefix alpha part and add on a sequential number. Because althougth the abc.... group of files would sequence to 0000049 the adf files would not. If you can help that would be fantastic