批量修改数据库内容

[yyouyou]

有一张表A，有两列，t1和t2
id t2
aaa myid:111
bbb yourid:222
ccc herid:3333

现在想通过批量替换，变成
id t2
aaa myid:aaa
bbb yourid:bbb
ccc herid:ccc

请教各位如何更改？

[hyde100]

update a
 set t2 = substring(t2,1,charindex(':',t2)) + id

[水族杰纶]

if object_id('tempdb..#')is not null drop table #
go
create table #(id varchar(10),    t2 varchar(20)) 
insert # select 'aaa'  ,  'myid:111' 
insert # select 'bbb' ,   'yourid:222' 
insert # select 'ccc' ,   'herid:3333' 
update # set t2=replace(replace(replace(t2,'111','aaa'),222,'bbb'),3333,'ccc')
select * from #
/*id         t2                   
---------- -------------------- 
aaa        myid:aaa
bbb        yourid:bbb
ccc        herid:ccc*/

[dawugui]

update a
set t2 = left(t2 , charindex(':')) + id

[xieyueqing]

[Quote=引用 1 楼 hyde100 的回复:]
SQL codeupdate a
set t2 = substring(t2,1,charindex(':',t2)) + id
[/Quote]

就用这个吧

[dawugui]

create table A(id varchar(10) ,   t2 varchar(20))
insert into a values('aaa' ,   'myid:111') 
insert into a values('bbb' ,   'yourid:222')
insert into a values('ccc' ,   'herid:3333') 
go

update a
set t2 = left(t2 , charindex(':' , t2)) + id

select * from a

drop table a

/*
id         t2                   
---------- -------------------- 
aaa        myid:aaa
bbb        yourid:bbb
ccc        herid:ccc

（所影响的行数为 3 行）
/*

[hyde100]

declare @a table
(
id varchar(50),
t2 varchar(50)
)

insert into @a
select 'aaa'  ,  'myid:111' union 
select 'bbb'   , 'yourid:222'union  
select 'ccc'   , 'herid:3333' 


update @a
 set t2 = substring(t2,1,charindex(':',t2)) + id

select * from @a

结果：
aaa	myid:aaa
bbb	yourid:bbb
ccc	herid:ccc

[水族杰纶]

if object_id('tempdb..#')is not null drop table #
go
create table #(id varchar(10),    t2 varchar(20)) 
insert # select 'aaa'  ,  'myid:111' 
insert # select 'bbb' ,   'yourid:222' 
insert # select 'ccc' ,   'herid:3333' 
update # set t2=left(t2,charindex(':',t2))+id
select * from #
/*id         t2                   
---------- -------------------- 
aaa        myid:aaa
bbb        yourid:bbb
ccc        herid:ccc*/

[yyouyou]

谢谢各位了O(∩_∩)O，马上给分

[ljhcy99]

UPDATE TABLE
SET t2=LEFT(CHARINDEX(':',T2)-1,T2)+ID

[ken119]

我也学习一下啦