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'
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 transelect * from sp
谢谢大家了,update tablename set sp_id =(case left(sp_id,1) when 'a' then 'p' when 'b' then 'k' else '' end ) +sp_id 我试过可以用
update tablename set sp_id ='k'+sp_id where left(sp_id,1)='b'
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 transelect * from sp
when 'b' then 'k' else '' end )
+sp_id
我试过可以用