SQL报错：列'ReceiverNo'多次指定，WHERE子句不能用别名求解决方案

问题原因 #

你碰到的错误是因为CTE location_4 里重复选了 ReceiverNo 列——一个来自ReceiverDET表，一个来自Receiver表。SQL不允许结果集里出现重复的列名，尤其是在要用来更新的CTE中，这就导致了报错。

解决方案 #

方案1：修正CTE写法并正确更新Estim表

先去掉CTE里重复的ReceiverNo，再通过关联CTE来更新目标表：

With location_4 as (
  SELECT TOP (1) 
    ReceiverDET.ReceiverNo,
    ReceiverDET.PartNo, 
    Receiver.ReceiveDate
  FROM Estim
  JOIN ReceiverDET ON Estim.PartNo = ReceiverDet.PartNo
  JOIN Receiver ON Receiver.ReceiverNo = ReceiverDET.ReceiverNo
  WHERE Estim.User_Text4 = ''
  ORDER BY Receiver.ReceiveDate DESC
)
UPDATE Estim
SET User_Text4 = 'Not in Location'
WHERE Estim.PartNo = (SELECT PartNo FROM location_4)
  AND Estim.User_Text4 = '';

方案2：直接关联表更新（更简洁）

不用CTE，直接通过表关联找到要更新的记录，这种写法更直观：

UPDATE TOP (1) Estim
SET User_Text4 = 'Not in Location'
FROM Estim
JOIN ReceiverDET ON Estim.PartNo = ReceiverDet.PartNo
JOIN Receiver ON Receiver.ReceiverNo = ReceiverDET.ReceiverNo
WHERE Estim.User_Text4 = ''
ORDER BY Receiver.ReceiveDate DESC;

额外说明 #

• 把原来逗号分隔表的写法换成JOIN，逻辑更清晰，也符合现代SQL的规范，避免隐含的笛卡尔积风险。
• 两种方案都是针对最新一条（按ReceiveDate降序）符合条件的Estim记录进行更新，和你原查询的逻辑一致。

内容的提问来源于stack exchange，提问作者Jaybbman