或者:update sysobjects set name = stuff(name,1,5,'SB_') where name in (select tname from bd_sccjxh where tname is not null )
select 'SB_' + substring(name,6,(datalength(name)-5)) from sysobjects where name in (select tname from bd_sccjxh where tname is not null ) 看看有多少行。 再: select distinct 'SB_' + substring(name,6,(datalength(name)-5)) from sysobjects where name in (select tname from bd_sccjxh where tname is not null ) 看看有多少行。
to zhlym(明) ( ) ,lisiyong(小样) , dutguoyi(新鲜鱼排): 1.更新name, 是可以拿name做為where的條件的。同样也否定了lisiyong(小样) 的说法, 如下即可以执行 update sysobjects set name = 'SB_' + substring(name,6,(datalength(name)-5)) where name in ('USER_ST31500_330','USER_ST31500_110') 2.'SB_' +substring(name,6,(datalength(name)-5)) 是不会有重复的,同样,substring(name,6,(datalength(name)-5)) 也没有有重复,原因是如下语句执行均正确而且已通过distinct语句测试过 update sysobjects set name = 'SB_' + name where name in (select tname from bd_sccjxh where tname is not null) 3。把datalength换成LEN并未成功,LEN是access的方法啊
select substring(name,6,(datalength(name)-5)) name from sysobjicts into #t where name in (select tname from bd_sccjxh where tname is not null ) select name,count(*) from #t group by name having count(*)>1 看下结果有没有重复的
所以改不了 不能在具有唯一索引 'ncsysobjects' 的对象 'sysobjects' 中插入重复键的行
看看substring(name,6,(datalength(name)-5)) 是否有重复
where name in (select tname from bd_sccjxh where tname is not null )
from sysobjects
where name in (select tname from bd_sccjxh where tname is not null )
看看有多少行。
再:
select distinct 'SB_' + substring(name,6,(datalength(name)-5))
from sysobjects
where name in (select tname from bd_sccjxh where tname is not null )
看看有多少行。
1.更新name, 是可以拿name做為where的條件的。同样也否定了lisiyong(小样) 的说法,
如下即可以执行
update sysobjects set name = 'SB_' + substring(name,6,(datalength(name)-5))
where name in ('USER_ST31500_330','USER_ST31500_110')
2.'SB_' +substring(name,6,(datalength(name)-5)) 是不会有重复的,同样,substring(name,6,(datalength(name)-5)) 也没有有重复,原因是如下语句执行均正确而且已通过distinct语句测试过
update sysobjects set name = 'SB_' + name
where name in (select tname from bd_sccjxh where tname is not null)
3。把datalength换成LEN并未成功,LEN是access的方法啊
where name in (select tname from bd_sccjxh where tname is not null )
select name,count(*) from #t group by name having count(*)>1
看下结果有没有重复的
是不是语法不对?没有怎么用过into噢
如下例:
SCAbcdefg
SCBdcdefg
两个表名称只有第三,第四个字符不同,如果执行update语句,则得到两个完全一样的新的表名,
SB_efg
SB_efg当然就不能修改啦。:)
update sysobjects set name = replace(name,'USER_','SB_')
where name in (select tname from bd_sccjxh where tname is not null )
谁说更新name, 而又拿name做為where的條件不行
SQL Server 啊 努力 奋斗-----------------------------------------------------
既然提示不能在具有唯一索引的对象中插入重复键的行,那么应该就是修改以后会出现重名的表。
而且 ycyin(齐格红茶) 朋友举出下面的例子也非常形象,给本人以引导,真心感谢
如下例:
SCAbcdefg
SCBdcdefg另外还要感谢lw1a2(一刀 [email protected]) 和wutao411(了缘) 两位朋友给出了不同的方法,可谓使本人开阔眼界,学会了举一反三也同样表示真心感谢,最后多谢各位捧场的朋友啊,^_^,现在结帖啦