“无法绑定由多个部分组成的标识符”，这是什么错误

[zerobegin_2]

with cte1 as
(
select BH as 'BH', min(sxh) as 'SXH',avg(PJF) as 'PJF' from KH_SubCompManager where nf='2011' group by BH
)
update KH_SubCompManager set DF=cte1.PJF where BH=cte1.BH and SXH=cte1.SXH

运行时提示错误：

消息 4104，级别 16，状态 1，第 1 行
无法绑定由多个部分组成的标识符 "cte1.BH"。
消息 4104，级别 16，状态 1，第 1 行
无法绑定由多个部分组成的标识符 "cte1.SXH"。

这是什么错误，这个错误又是什么意思？？？

[路过秋天-博客园]

这个错误就是表的前缀用错了。
比如这句：update KH_SubCompManager set DF=cte1.PJF where BH=cte1.BH and SXH=cte1.SXH
cte1或者其它，必须在全局的语句里出现，如果没或者非全局，就报错了。

另外操作数据库推荐：cyq.data,试一下，爽歪歪。

[tomysea]

你用的是CTE（Common Table Expression）
问题在于，你在UPDATE语句中使用了KH_SubCompManager和cte1这两个表来确定更新的范围，却没有指定FROM子名，所以sql解析器是并不知道cte1为何物。

正确做法：

with cte1 as
(
    select BH as 'BH', min(sxh) as 'SXH',avg(PJF) as 'PJF' 
    from KH_SubCompManager where nf='2011' group by BH
)
UPDATE KH_SubCompManager SET DF=cte1.PJF 
FROM KH_SubCompManager s INNER JOIN cte1 c
ON s.BH=c.BH AND s.SXH=c.SXH

注意UPDATE语句的用法

[lovemax14]

通常都是联结没有做好，就是 XX.字段 的XX打错了。

这都是逻辑上的问题，你理清一下思路，就很容易发现哪里出问题了。

[huangwenquan123]

update KH_SubCompManager set DF=cte1.PJF where BH=cte1.BH and SXH=cte1.SXH

你这里读的表没有用到你上面写的cte
但是却想用cte取到的内容。

[子夜__]

update KH_SubCompManager set DF=cte1.PJF from KH_SubCompManager khs inner join cte1 c on khs.BH=c.BH and khs.SXH=c.SXH

这样看看

[sswp7]

[Quote=引用 13 楼 ssp2009 的回复:]

SQL code

--你这个是有问题的，没有数据，应该有一对多的关系
update a set DF=(select top 1 PJF from cte1 where BH=a.BH and SXH=a.SXH)
from KH_SubCompManager a
[/Quote]

高手。

[sswp7]

[Quote=引用 14 楼 tomysea 的回复:]

引用 12 楼 zerobegin_3 的回复:
引用 8 楼 ssp2009 的回复:

SQL code
with cte1 as
(
select BH as BH, min(sxh) as SXH,avg(PJF) as PJF
from KH_SubCompManager where nf='2011' group by BH
)
update KH_SubCompM……
[/Quote]

赞。

[zerobegin_2]

with cte1 as
(
select BH as 'BH', min(sxh) as 'SXH',avg(PJF) as 'PJF'
from KH_SubCompManager where nf='2011' group by BH
)
UPDATE KH_SubCompManager SET DF=cte1.PJF
FROM KH_SubCompManager s INNER JOIN cte1 c
ON s.BH=c.BH AND s.SXH=c.SXH

这样不行啊。提示错误：

消息 4104，级别 16，状态 1，第 1 行
无法绑定由多个部分组成的标识符 "cte1.PJF"。

[zerobegin_2]

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

update KH_SubCompManager set DF=cte1.PJF from KH_SubCompManager khs inner join cte1 c on khs.BH=c.BH and khs.SXH=c.SXH

这样看看
[/Quote]

消息 4104，级别 16，状态 1，第 1 行
无法绑定由多个部分组成的标识符 "cte1.PJF"。

[快溜]

with cte1 as
(
  select BH as BH, min(sxh) as SXH,avg(PJF) as PJF 
      from KH_SubCompManager where nf='2011' group by BH
)
update KH_SubCompManager set DF=cte1.PJF 
from KH_SubCompManager,cte1
where KH_SubCompManager.BH=cte1.BH and KH_SubCompManager.SXH=cte1.SXH

[快溜]

as别名不要加单引号

[zerobegin_3]

[Quote=引用 9 楼 ssp2009 的回复:]

as别名不要加单引号
[/Quote]

with cte1 as
(
select BH as BH, min(sxh) as SXH,avg(PJF) as PJF
from KH_SubCompManager where nf='2011' group by BH
)
update KH_SubCompManager set DF=cte1.PJF from KH_SubCompManager khs inner join cte1 c on khs.BH=c.BH and khs.SXH=c.SXH


消息 4104，级别 16，状态 1，第 1 行
无法绑定由多个部分组成的标识符 "cte1.PJF"。

还是不行啊。。。

[zerobegin_3]

[Quote=引用 8 楼 ssp2009 的回复:]

SQL code
with cte1 as
(
select BH as BH, min(sxh) as SXH,avg(PJF) as PJF
from KH_SubCompManager where nf='2011' group by BH
)
update KH_SubCompManager set DF=cte1.PJF
from KH_SubCompManage……
[/Quote]

可以了，为什么呢，这是什么写法。

[zerobegin_3]

[Quote=引用 8 楼 ssp2009 的回复:]

SQL code
with cte1 as
(
select BH as BH, min(sxh) as SXH,avg(PJF) as PJF
from KH_SubCompManager where nf='2011' group by BH
)
update KH_SubCompManager set DF=cte1.PJF
from KH_SubCompManage……
[/Quote]

可以了，为什么呢，这是什么写法。

[快溜]

--你这个是有问题的，没有数据，应该有一对多的关系
update a set DF=(select top 1 PJF from cte1 where BH=a.BH and SXH=a.SXH)
from KH_SubCompManager a

[tomysea]

[Quote=引用 12 楼 zerobegin_3 的回复:]
引用 8 楼 ssp2009 的回复:

SQL code
with cte1 as
(
select BH as BH, min(sxh) as SXH,avg(PJF) as PJF
from KH_SubCompManager where nf='2011' group by BH
)
update KH_SubCompManager set DF=cte1.PJF
fro……
[/Quote]

update KH_SubCompManager set DF=cte1.PJF from KH_SubCompManager khs inner join cte1 c on khs.BH=c.BH and khs.SXH=c.SXH

注意红色部分，你已经在join条件中为表cte1指定了别名c，那么在其它地方引用cte1时，只能用别名c,即cte1.PJF应为c.PJF

若你没有指定别名，如#9楼一样，就用全名引用其列

[zerobegin_3]

[Quote=引用 8 楼 ssp2009 的回复:]

SQL code
with cte1 as
(
select BH as BH, min(sxh) as SXH,avg(PJF) as PJF
from KH_SubCompManager where nf='2011' group by BH
)
update KH_SubCompManager set DF=cte1.PJF
from KH_SubCompManage……

http://topic.csdn.net/u/20110812/10/06196450-1773-49b0-b4a7-ecd4ae763f89.html

谢谢。。。
[/Quote]

[zerobegin_3]

[Quote=引用 14 楼 tomysea 的回复:]

引用 12 楼 zerobegin_3 的回复:
引用 8 楼 ssp2009 的回复:

SQL code
with cte1 as
(
select BH as BH, min(sxh) as SXH,avg(PJF) as PJF
from KH_SubCompManager where nf='2011' group by BH
)
update KH_SubCompM……
[/Quote]

是的谢谢。