大家帮忙看看，这个语句怎么写？

[zkh7912]

表中有一列是sp_id(字符型8位),想在这一列所有以a开头的字段前加p,以b开头的字段前加k,例如，a0001,a1002都改为pa0001,pa1002. b0003,b0120都改为kb0003,kb0120.大家怎么改的，说说？先谢谢了。

[zheninchangjiang]

update tablename set sp_id =(case left(sp_id,1) when 'a' then 'p'
when 'b' then 'k' else '' end )
+sp_id

[dafu71]

update tablename set sp_id ='p'+sp_id where left(sp_id,1)='a'
update tablename set sp_id ='k'+sp_id where left(sp_id,1)='b'

[askgwf2004]

if Object_id('sp') is not null drop table sp
create table sp(
sp_id varchar(8),
)
insert sp select 'a0001' union
select 'a0002' union
select 'b0003' union
select 'b00120' union
select 'c0021'

begin tran
if object_id('tempdb..#k') is not null drop table #k
select case when left(sp_id,1)='a' then 'p'+sp_id when left(sp_id,1)='b' then 'k'+sp_id else sp_id end as sp_id into #k from sp
delete sp
insert sp select * from #k
commit tran
if @@trancount>0 rollback tran

select * from sp

[zkh7912]

谢谢大家了，update tablename set sp_id =(case left(sp_id,1) when 'a' then 'p'
when 'b' then 'k' else '' end )
+sp_id
我试过可以用