导航
  • 主页
  • 基础类
  • 应用实例
  • 新技术前沿

批量修改数据库内容

yyouyou 2008-12-01 09:38:59
有一张表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

请教各位如何更改?
...全文
38 点赞 收藏 10
写回复
10 条回复
切换为时间正序
请发表友善的回复…
发表回复
ken119 2008-12-13
我也学习一下啦
回复
ljhcy99 2008-12-01


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

回复
yyouyou 2008-12-01
谢谢各位了O(∩_∩)O,马上给分
回复
水族杰纶 2008-12-01
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*/
回复
hyde100 2008-12-01
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
回复
dawugui 2008-12-01
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 行)
/*
回复
xieyueqing 2008-12-01
[Quote=引用 1 楼 hyde100 的回复:]
SQL codeupdate a
set t2 = substring(t2,1,charindex(':',t2)) + id
[/Quote]

就用这个吧
回复
dawugui 2008-12-01
update a
set t2 = left(t2 , charindex(':')) + id
回复
水族杰纶 2008-12-01
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*/
回复
hyde100 2008-12-01
update a
set t2 = substring(t2,1,charindex(':',t2)) + id
回复
发动态
发帖子
MS-SQL Server
创建于2007-09-28

3.2w+

社区成员

MS-SQL Server相关内容讨论专区
申请成为版主
社区公告
暂无公告