多个相同结构表查询后delete的问题

[drifter250771]

http://topic.csdn.net/u/20110527/00/5d474a6b-274b-41fb-b1b9-00a2c9f9a563.html

多个相同结构表查询的问题，已成功解决。

现在要把操作换成delete，求解。

[drifter250771]

没有更多的人来关注了吗？

[快溜]

declare @sql varchar(8000)
set @sql=''
select --忘记加了
  @sql=@sql+'  delete from '+'['+name+']'+'where FDNM=123456'  
from sysobjects 
where xtype= 'U' and name like 'TBNM%'

exec (@sql)

[AcHerat]

--05

declare @sql varchar(8000)
select @sql = isnull(@sql+'  ','') + ' delete from ' + [name] + ' where FDNM=123456'  
from sysobjects 
where xtype= 'U' and name like 'TBNM%'
exec (@sql)

[btxp163]

涩狼、小三都关注了，那咱只有打酱油的份了


缺分……

[drifter250771]

问题解决，感谢各位，结贴!

[快溜]

declare @sql varchar(8000)
select 
  @sql=isnull(@sql+'union all select * from ','select * from ')+'['+name+']'  
from sysobjects 
where xtype= 'U' and name like 'TBNM%'

set @sql='delete from ('+@sql+') t where FDNM=123456'

--print @sql

exec (@sql)

[叶子]

declare @sql varchar(8000)
select 
  @sql=isnull(@sql+'; delete from ','delete from ')+'['+name+']'  
from sysobjects 
where xtype= 'U' and  left(name,4)='TBNM' 

--select @sql
/*
delete from [TBNM1]; delete from [TBNM2]; delete from [TBNM5]
*/
exec (@sql)

[打一壶酱油]

declare @sql varchar(8000)
select 
  @sql=isnull(@sql+'union all select * from ','select * from ')+'['+name+']'  
from sysobjects 
where xtype= 'U' and name like 'TBNM%'

set @sql=' delete from from ('+@sql+') t where FDNM=123456'


exec (@sql)

[打一壶酱油]

--还是改成 max 比较好
declare @sql varchar(max)
select 
  @sql=isnull(@sql+'union all select * from ','select * from ')+'['+name+']'  
from sysobjects 
where xtype= 'U' and name like 'TBNM%'

set @sql=' delete from from ('+@sql+') t where FDNM=123456'


exec (@sql)

[快溜]

declare @sql varchar(8000)
set @sql=''
select 
  @sql='  delete from '+'['+name+']'+'where FDNM=123456'  
from sysobjects 
where xtype= 'U' and name like 'TBNM%'

exec (@sql)

[ly745455]

MARK

[drifter250771]

一三四楼都是select *到delete的简单替换，这是无效的，要不然我也不会发贴了。

二楼代码不完整，条件where FDNM=123456都没有，我就没测试。

五楼代码测试结果是：delete仅仅对最后一张TBNM****表进行了操作，前面的表都没动。