对每个表做下操作:update table1 set col1='我的客户2' where col1='我的客户1'
update table2 set col1='我的客户2' where col1='我的客户1'
update table3 set col1='我的客户2' where col1='我的客户1'...
update table2 set col1='我的客户2' where col1='我的客户1'
update table3 set col1='我的客户2' where col1='我的客户1'...
解决方案 »
- 谁能解释下这段代码?
- 如何用SQL语句清空MSSQL数据库?
- 能用min()的方法吗?谢谢了
- 数据库表的字段值被改,如何恢复和查原因
- 为什么我得MSSQL2000数据库用本地服务器名可以连接数据库,用IP地址却不能连接数据库了呢???
- 急急数据库坏了,没有备份
- 求助sql语句
- sqlserver释放物理内存以及碎片整理的问题。高手请看
- 怎么没人回答我啊????????
- 我原来的win2000server崩掉了,现在是win2000pro,请问怎样装sql server2000才能恢复原来的数据呢?
- 为什么这个函数不能执行?
- ...........请pengdali高手帮忙............
比如说A表中可能会有某几条记录的"KHMC"字段值为"我的客户2"
而B表中则是有几条记录的"GSMC"字段值为"我的客户2"
所以这个遍历的方法应该是比较复杂的,高手来看一下,帮帮忙
update table1 set 字段='我的客户2' where 字段='我的客户1'
.....
这种情况好办!
update B set GSMC='我的客户2' where GSMC='我的客户1'
2.A表中可能会有某几条记录的"KHMC"字段值为"我的客户2"
这种我想到的办法是这样,有点麻烦!但是只要执行一次!
思路:
update A set a.COL1=REPLACE(A.COL1,'我的客户2','我的客户1'),
a.COL2=REPLACE(A.COL2,'我的客户2','我的客户1'),
...
以后也可以用,不用修改你的sql语言,因为只要是字符串的Field你都写进去,不会有问题!
有则更新,没有也不会改变原来的值
FYI
declare @oldstr varchar(100)
set @oldstr='原字符串' --原字符
declare @newstr varchar(100)
set @newstr='新字符串' --新字符declare @s varchar(8000)
declare tb cursor local for
select s='if exists(select 1 from ['+b.name+'] where ['+a.name+'] like ''%'+@oldstr+'%'')
update ['+b.name+'] set ['+a.name+']='''+@newstr+''' where ['+a.name+']='''+@oldstr+''' '
from syscolumns a join sysobjects b on a.id=b.id
where b.xtype='U' and a.status>=0
and a.xusertype in(175,239,231,167)
open tb
fetch next from tb into @s
while @@fetch_status=0
begin
exec(@s)
fetch next from tb into @s
end
close tb
deallocate tb--我的Bolg提到这个问题:参考
--http://blog.csdn.net/zlp321002/archive/2005/06/16/395621.aspx
@fieldname varchar(50),
@child_nodeid varchar(50),
@child_mytest varchar(50),
@tempsql varchar(1000), --临时sql语句(中间结果)
@sql nvarchar(4000) --最后要执行SQL语句set @tempsql = ''
set @sql=''DECLARE table_cursor CURSOR FOR --取得当前数据库中所有用户表表名和字段名
SELECT
d.name tablename,
a.name fieldname
FROM syscolumns a left join systypes b
on a.xtype=b.xusertype
inner join sysobjects d
on a.id=d.id and d.xtype='U' and d.name<>'dtproperties'OPEN table_cursorFETCH NEXT FROM table_cursor
INTO @tablename,@fieldnameWHILE @@FETCH_STATUS = 0
BEGIN
set @tempsql = 'update ' + @tablename + ' set ' + @fieldname + '=' + '''' + '我的客户2' + '''' + ' where ' + @fieldname + '=' + '''' + '我的客户1' + ''''
-- 获得下一条记录
FETCH NEXT FROM table_cursor
INTO @tablename,@fieldname
set @sql = @sql + @tempsql + ';'
ENDCLOSE table_cursor
DEALLOCATE table_cursorprint @sql
测试用的忘改了
declare @name varchar(80)
select @loop = count(*) from sysobjects where type = 'U'set @i = 1
while @i <= @loop
begin
if @i = 1
select top 1 @name = name
from sysobjects
where type = 'U'
order by name
else
select top 1 @name = name
from sysobjects
where name > @name and type = 'U'
order by name
set @i = @i + 1
declare @colname varchar(40),
@loop2 int,
@i2 int,
@coltype varchar(10)
select @loop2 = count(*) from syscolumns where id = object_id(@name)
set @i2 = 1
while @i2 <= @loop2
begin
if @i2 = 1
select top 1 @colname = a.name,@coltype = b.name
from syscolumns a,systypes b
where a.id = object_id(@name) and a.xtype = b.xtype
order by a.name
else
select top 1 @colname = a.name,@coltype = b.name
from syscolumns a,systypes b
where a.id = object_id(@name) and a.xtype = b.xtype and a.name > @colname
order by a.name
declare @sql nvarchar(1000)
if @coltype in ('char','nchar','varchar','nvarchar','text')
begin
set @sql = 'update '+ @name +' set ' + @colname +'= ''gggggg'' where ' +
@colname + '=''bbb'''
select @sql
exec(@sql)
end
set @i2 = @i2 + 1
end
end