子查询返回的值不止一个。当子查询跟随在 =、!=、<、<=、>、>= 之后，或子查询用作表达式时，这种情况是不允许的。

[wangwuaowangzhen]

update T_EquipmentTally
set tallyNumber=(select dbo.getLefttallynumber(tallyName,tallyid )+
shorttitle+'-'+(select shorttitle +'-'+dbo.gettallynumber(tallyName,tallyid )
from T_Organization inner join T_EquipmentTally on T_Organization.orgId=T_EquipmentTally.manageStationId)
from T_RoadInfo inner join T_EquipmentTally on T_RoadInfo.roadId=T_EquipmentTally.pertainRoad )
where tallyNumber like 'YHDIC-2012%'


错误提示 子查询返回的值不止一个。当子查询跟随在 =、!=、<、<=、>、>= 之后，或子查询用作表达式时，这种情况是不允许的。

[wangwuaowangzhen]

用了两个函数

CREATE FUNCTION gettallyNumber
(@name varchar(200),@id int)
RETURNS varchar(200) AS
begin
Declare @S1 varchar(100)
Select @S1=tallynumber from T_EquipmentTally where tallyId=@id and tallyName=@name;
--Select Substring(@S1,charindex('YHDIC',@S1)+12,Len(@S1))
--select RIGHT(@S1,7);
return substring(@S1,18,7)
end




create FUNCTION getLefttallyNumber
(@name varchar(200),@id int)
RETURNS varchar(200) AS
begin
Declare @S1 varchar(100)
Select @S1=tallynumber from T_EquipmentTally where tallyId=@id and tallyName=@name;
--Select Substring(@S1,charindex('YHDIC',@S1)+12,Len(@S1))
--select RIGHT(@S1,7);
return substring(@S1,1,6)
end

[开着拖拉机泡妞]

(select dbo.getLefttallynumber(tallyName,tallyid )+
shorttitle+'-'+(select shorttitle +'-'+dbo.gettallynumber(tallyName,tallyid )
你的这个是不是返回的不是唯一的一个值？？

[AcHerat]

(select dbo.getLefttallynumber(tallyName,tallyid )+
shorttitle+'-'+(select shorttitle +'-'+dbo.gettallynumber(tallyName,tallyid )
from T_Organization inner join T_EquipmentTally on T_Organization.orgId=T_EquipmentTally.manageStationId)

这个东东相对应的可能有多个，更新前查询下 select 看 tallyNumber 字段更新的值是否有多个，没有就加个 top 1 试试。

[wangwuaowangzhen]

是的，返回的不是一个，我是要修改很多条数据，从三个表里查询，拼成一个字段

[开着拖拉机泡妞]

[Quote=引用 4 楼 的回复:]

是的，返回的不是一个，我是要修改很多条数据，从三个表里查询，拼成一个字段
[/Quote]
返回不止一个的时候你要想办法找个关联关系让他一一对应起来。

[wangwuaowangzhen]

[Quote=引用 5 楼 的回复:]
引用 4 楼 的回复:

是的，返回的不是一个，我是要修改很多条数据，从三个表里查询，拼成一个字段

返回不止一个的时候你要想办法找个关联关系让他一一对应起来。
[/Quote]

就是这个问题，我是用主外键查出来的，应该对啊，为什么还是提示，错误啊

[开着拖拉机泡妞]

既然报出错误了肯定就是存在的啊。你仔细利利关系，要确定更新的时候不要把多个值同时赋给一个字段，那么肯定会报错

[孤独加百列]

改种格式试试

WITH T AS
(
select dbo.getLefttallynumber(tallyName,tallyid )+ shorttitle + '-' + ( select shorttitle + '-' + dbo.gettallynumber(tallyName,tallyid )  
																		from T_Organization inner join T_EquipmentTally on T_Organization.orgId = T_EquipmentTally.manageStationId ) AS value,T_EquipmentTally.关键字
from T_RoadInfo inner join T_EquipmentTally on T_RoadInfo.roadId=T_EquipmentTally.pertainRoad )
update T_EquipmentTally  
set tallyNumber= T.value
FROM T
where tallyNumber like 'YHDIC-2012%' AND T.关键字 = T_EquipmentTally.关键字

[wangwuaowangzhen]

[Quote=引用 7 楼 travylee 的回复:]
既然报出错误了肯定就是存在的啊。你仔细利利关系，要确定更新的时候不要把多个值同时赋给一个字段，那么肯定会报错
[/Quote]

update T_EquipmentTally
set tallyNumber=(select dbo.getLefttallynumber(tallyName,tallyid )+
shorttitle+'-'+(select shorttitle +'-'+dbo.gettallynumber(tallyName,tallyid )
from T_Organization inner join T_EquipmentTally on T_Organization.orgId=T_EquipmentTally.manageStationId)
from T_RoadInfo inner join T_EquipmentTally on T_RoadInfo.roadId=T_EquipmentTally.pertainRoad )
where tallyNumber like 'YHDIC-2012%'

如果我把两个函数和其中任何一个连接起来，就不报错，如果按上边的就报错，原因是不是在于两个select语句查出的语句不一样多，第一个查出10000多条，第二个查出30000多条，是不是这个原因啊

[tim_spac_126]

update T_EquipmentTally set
    tallyNumber=
            dbo.getLefttallynumber(tallyName,tallyid)+
            shorttitle+'-'+
            shorttitle+'-'+
            dbo.gettallynumber(tallyName,tallyid)
from T_EquipmentTally
inner join T_RoadInfo on T_RoadInfo.roadId=T_EquipmentTally.pertainRoad 
where tallyNumber like 'YHDIC-2012%'

?

[wangwuaowangzhen]

我是根据主表中的两个字段查出另外两张表的信息，其中有一个字段，有的时候没有数据，另一个字段长期有数据，是不是这个原因？

[开着拖拉机泡妞]

你两个函数都的跟被更新的一一对应，这样才能确保更新的值正确

[wangwuaowangzhen]

[Quote=引用 10 楼 的回复:]
SQL code

update T_EquipmentTally set
tallyNumber=
dbo.getLefttallynumber(tallyName,tallyid)+
shorttitle+'-'+
shorttitle+'-'+
dbo.gettallynum……
[/Quote]

shorttitle这个存在于两张表中，

[wangwuaowangzhen]

[Quote=引用 12 楼 的回复:]
你两个函数都的跟被更新的一一对应，这样才能确保更新的值正确
[/Quote]

对应这块，老是写不对

[AcHerat]

update b
set b.tallyNumber = dbo.getLefttallynumber(tallyName,tallyid )+
		shorttitle+'-'+shorttitle +'-'+dbo.gettallynumber(tallyName,tallyid)
-- = 后边字段注明所属表别名
from T_RoadInfo a join T_EquipmentTally b on a.roadId=b.pertainRoad
	join T_Organization c on c.orgId = b.manageStationId
where b.tallyNumber like 'YHDIC-2012%'

这样看看，记得是测试哦~！

[wangwuaowangzhen]

[Quote=引用 15 楼 的回复:]
SQL code


update b
set b.tallyNumber = dbo.getLefttallynumber(tallyName,tallyid )+
shorttitle+'-'+shorttitle +'-'+dbo.gettallynumber(tallyName,tallyid)
-- = 后边字段注明所属表别名
from T_RoadInfo……
[/Quote]


违反了 UNIQUE KEY 约束 'UQ__T_EquipmentTally__1273C1CD'。不能在对象 'dbo.T_EquipmentTally' 中插入重复键。