Question

如何批量修改数据库中不同字段中相同的值

数据库中有3个表分别为A ,B, C
A表中有两个字段A1 A2
B表中有两个字段B1,B2
C表中有三个字段C1,C2,C3
这些字段中都存在有abc这样的数据，现在请问高手，如何批量删除这三个表中存在有abc这样的数据，请指教谢谢！

Answer 1

use test --修改为你的数据库名称
declare tb_name cursor for select NAME,ID from sysobjects WHERE TYPE='U' --得到用户表的ID
declare @in_char varchar(20)

set @in_char='%<script>%'
---查找注入字符串
declare @tj varchar(5000)
set @tj=' where '
declare @i int
set @i=0
declare @sql varchar(200)
DECLARE @table_name varchar(50)
DECLARE @table_id varchar(50)
DECLARE @feild_name varchar(50)
DECLARE @fd_count int
open tb_name
FETCH next FROM tb_name into @table_name,@table_id
while
@@FETCH_STATUS=0

begin

set @i=0
declare fd_name cursor for SELECT NAME FROM syscolumns
where id=@table_id and type=39 group by name--得到用户表某一ID的所有字符类型字段名称
open fd_name
FETCH next FROM fd_name into @feild_name
print @fd_count

while
@@FETCH_STATUS=0

begin

set @i=@i+1
set @tj=@tj+@feild_name +' like '''+ @in_char +'''' +(case when (SELECT count(type) as fd_count FROM syscolumns
where id=@table_id and type=39)
>@i then ' or ' else ' 'end)
FETCH next FROM fd_name into @feild_name

end
-- print @tj
if @tj!=''
begin
SET @SQL='DELETE FROM '+@TABLE_NAME+@TJ
print '切记先运行代码看print 出来的SQL确认后再删除数据，即将运行的代码是 '
print '===================================================================='
print @SQL
print '===================================================================='
--切记先运行代码看print 出来的SQL确认后再删除数据
exec(@sql) --修改注释后删除数据
end
set @tj=''
CLOSE fd_name
deallocate fd_name
FETCH next FROM tb_name into @table_name,@table_id
end
close tb_name
deallocate tb_name

Answer 2

最简单的写法~delete from A where A1 like '%abc%' or A2 like '%abc%'delete from B where B1 like '%abc%' or B2 like '%abc%'delete from C where C1 like '%abc%' or C2 like '%abc%' or C3 like '%abc%'